please help with my macro for if function

A

artyguns

Joined
Nov 15, 2012
Messages
4
Reaction score
0
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.

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 macro. then the existing column J becomes column K and so on.

What am I missing here?

Hope you'll be able to help.

Thanks.
 
your case isn't so clear
but if you're trying to get the value for example for cell (G3) you can't just type it
you should say this

if Sheets("SheetName").Cells(RowNumber,ColumnNumber)<>0 then
Sheets("SheetName").Cells(RowNumber,ColumnNumber).Value=Sheets("SheetName").Cells(RowNumber2,ColumnNumber2)*Sheets("SheetName").Cells(RowNumber3,ColumnNumber3)
Else
Sheets("SheetName").Cells(RowNumber,ColumnNumber).Value=Sheets("SheetName").Cells(RowNumber4,ColumnNumber4)*Sheets("SheetName").Cells(RowNumber5,ColumnNumber5)
End if


Where RowNumbers are the index of the cells you want to get value from
like in your example are I3, G3, H3
where G3 index is Cells(3,6) Row and Column index Starts from Zero

I hope this made your problem clear
 
artyguns said:
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.

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 macro. then the existing column J becomes column K and so on.

What am I missing here?

Hope you'll be able to help.

Thanks.
Click to expand...

Hello

Thnaks for your advice. I'll give it atry and let you know.
 
Hi
It works but it's overriding the value in the existing cells. I need to insert a new column for the macro. This means where I want the macro to sit, the column has to move to the right.

I also need to handle variable row counts

Any idea?
 
Last edited:
well,,
you can read this previous post by me about excel vba reading Rows and writing Values
https://www.pcreview.co.uk/forums/excel-vba-help-t4053800.html

and you can define the new column counter as variable or just write the index
i mean

the example above have the area ("A:E") which is 5 Columns
in ur case you'll add one more column, so it will be from ("A:F")
and then


Sheets("Sheet2").Cells(nRowCounter)(6).Value =

and thats it
any further questions just ask :)
 
Thanks a lot.

As you can see I am new to macros and VBAs. Can you recommend some good books?

Thanks,
 
before 2 months i didn' t even know that i could write VB Code in excel
and i've learned it through the internet

thats my source
so unfortuantly i don't know any books
but if you're a programmer then you'll find that its the same concept of VB.Net programming with some differences
 
Back
Top