Copy Down Formulas to Last Row-CodePudding

I have an Excel workbook that I run from a reporting tool.

In the worksheet "Data", in cells I2, J2, K2, L2, and M2 I have a formulas.

I am looking for a VBA script that will drag the formulas in these cells to where ever the last row may be. Col A can be used to determine the last row number.

I have been looking for a script that works, but I am struggling to find one. Please may someone assist me with this. Below screenshot of the static formulas that sit in these 5 cells.

Range("I2:I" & LastRow).Formula = "=IF(L2=1,IF(F2-E2>0,E2,IF(F2-E2<=0,F2,0)),IF(F2-E2>0,E2,IF(F2-E2<=0,F2-I1,0)))"

CodePudding user response：

Assuming your formula works, maybe try :

Lastrow = Range("H" & Rows.Count).End(xlUp).Row
Range("I2") = "=IF(L2=1,IF(F2-E2>0,E2,IF(F2-E2<=0,F2,0)),IF(F2-E2>0,E2,IF(F2-E2<=0,F2-I1,0)))"
Range("I2:I" & LastRow).FillDown

CodePudding user response：

Enter the formula into I2 :

Range("I2").Formula = "=IF(L2=1,IF(F2-E2>0,E2,IF(F2-E2<=0,F2,0)),IF(F2-E2>0,E2,IF(F2-E2<=0,F2-I1,0)))"

Then use a separate line to autofill to last row

Range("I2").AutoFill Range("I2:I" & LastRow)

So like this :

    Range("I2").Formula = "=IF(L2=1,IF(F2-E2>0,E2,IF(F2-E2<=0,F2,0)),IF(F2-E2>0,E2,IF(F2-E2<=0,F2-I1,0)))"
    Range("I2").AutoFill Range("I2:I" & LastRow)