VBA - Enter formula into cell and then copy down total number of rows defined by another column

[jonny1984]

Hi there

Looking for some help - at the moment I have a VBA code to insert a formula into a cell (J5) . That formula is below .....

What I am looking to do is have the Macro

1) Calculate the total number of rows which have some data in them in Column C
2) Copy the below formula down from J5 .. to J-123 .. (whatever row 123 is as defined by step 1)

Here is my current formula which just enters the formula into cell J5 only

Worksheets(“Data”).Range(“J5”).Formula = "=IF(H5=""a"",I5*(1-Master!$C$5),IF(H5="”b”",I5*(1+Master!$C$5)))"


Any help much appreciated !! Thank you in advance !!!

 

[Joe4]

Try this:

Sub MyCopy()

    Dim lr As Long
    
'   Find last row with data in column C
    lr = Sheets("Data").Cells(Rows.Count, "C").End(xlUp).Row

'   Enter formula in column J
    Sheets("Data").Range("J5:J" & lr).FormulaR1C1 = _
        "=IF(RC[-2]=""a"",RC[-1]*(1-Master!R5C3),IF(RC[-2]=""b"",RC[-1]*(1+Master!R5C3)))"
        
End Sub

 

[jonny1984]

Thanks Joe !!

 

[Joe4]

You are welcome!

Note: The easy way to get the formula in that R1C1 formula so you can apply it to multiple cells at once is to simply turn on the Macro Recorder, and record yourself entering the formula into J5. That will give you the code you need for that formula.