IF Function in VBA
I managed to create a macro to show the following:
If (I3<>0,I3*G3,H3*G3) and this repeats itself for cell N3,R3, V3,Z3 ETC.
Option Explicit
Sub Eg()
Range("J3, N3,R3, V3,Z3,AD3,AH3,AL3,AP3,AT3,Ax3,BB3,XF3,BJ3").Formula = "=IF(RC[-1]<>0,RC[-1]*RC[-3],RC[-2]*RC[-3])"
End Sub
Let me explain a bit more how this should work:
This report needs to be downloaded from an application.
The macro needs to be attached to this report so that when I download the report the macro automatically runs this formula in the appropriate columns.
Also I 'll have to populate the spreadhseet for all the rows with this formula.
The columns where the formula should sit are not blank but this needs to be catered for in the report automatically once the macro is run.
What am I missing here?
Hope you'll be able to help.
Thanks.
| Attachment | Size |
|---|---|
| Example_Actual_Spend_on_Time_Sheet_16_11_v1.xls | 40.5 KB |
Thanks for this ...
I'll give it a try and let you know how it goes.
Thanks again for this.
You can attach file here
Hi,
You can attach a file here.
To attach a file:
1) Log In;
2) Go to your forum topic -> Edit tab;
3) Into File attachments section attach your file (browse your file and then press Attach button);
4) Save your topic.
Best regards.
IF Function in VBA
Thank you Manny.
Ok, I suppose that your
Ok, I suppose that your application generates a spreadsheet as text data ; if it's by design in this way, the behavior is normal.
Can you explain what is this application ?
What kind of process generates the workbook ?
Anyway, in this case, you will not be able to put the formula as you want to do.
You must forsee to generate a template with for example, an autoexec process that comes from the Workbook_Open() event.
This process will arrange the spreadsheet as you expect.
IF Function in VBA
Hi,
What's wrong ?
I believe I don't understand what you really want finally...
into J3 : =IF(I3<>0;I3*G3;H3*G3)
...
into Z3 : =IF(Y3<>0;Y3*W3;X3*W3)
...
into AX3 : =IF(AW3<>0;AW3*AU3;AV3*AU3)
and so on...
Your formula seems to work correctly ; can you explain what you want to obtain at the end.
Regards
Argy
IF Function in VBA
This report needs to be downloaded from an application. The macro needs to be attached to this report so that when I download the report the macro automatically runs this formula in the appropriate columns. Also I 'll have to populate the spreadhseet for all the rows with this formula. The columns where the formula should sit are not blank but this needs to be catered for in the report automatically once the macro is run. I hope this makes sense. Hope you'll be able to helP. Thanks again Silky.
IF Function in macro
Hi Argy
Is there a way I can send you an attachment where I can explain my report? you will understand it better...
Thanks for letting me know.
IF Function in macro
when I click on the command button, the cell J3 is not updated with the formula. There's already data in this cell.
I have to create the formula so that there's another value in this cell when I run the macroThen the exisiting column J becomes column K and so on.
I ahve attached an example of the file as it is without any macro.
Ok, I suppose that your
Ok, I suppose that your application generates a spreadsheet as text data ; if it's by design in this way, the behavior is normal.
Can you explain what is this application ?
What kind of process generates the workbook ?
Anyway, in this case, you will not be able to put the formula as you want to do.
You must forsee to generate a template with for example, an autoexec process that comes from the Workbook_Open() event.
This process will arrange the spreadsheet as you expect.
IF Function in VBA
Very true , I'll need a template to attach to the report that sits on the application.
The application is for Vendor Management software and there's a module where you can craete all the reports but it's simple drag and drop fields so you can't create macros in them.
but the output can be in pDF/Excel or csv format.
I don't understand why this frmula is not working.it's not populating the cells I want with the new values.