VBA Formula Autofill

[zollan23]

Hello -

I am currently trying to run a macro and I need a formula autofilled to the end of my data set and the data set will be a different length each time. I'm trying to enter a formula in cell P2 and I want it to copy down all the way until the data ends.

Here's what I've tried:

Dim LR As Long
LR = Range("P2").End(xlDown).Row
Range("P2:P" & LR).FormulaR1C1 = "=IF(AND(RC[-4]>-1,RC[-4]<0.1),""Lowest CTR"",IF(AND(RC[-4]>0.1,RC[-4]<0.2),""Extremely Low"",IF(AND(RC[-4]>0.2,RC[-4]<0.3),""Very Low"",IF(AND(RC[-4]>0.3,RC[-4]<0.4),""Low"",IF(AND(RC[-4]>0.4,RC[-4]<0.6),""Below Average"",IF(RC[-4]=0.1,""Lowest"",IF(RC[-4]=0.2,""Extremely Low"",IF(RC[-4]=0.3,""Very Low"",IF(RC[-4]=0.4,""Low"",IF(OR(RC[-4]=0.5,RC[-4]=0.6),""Below Average"",""Error""))))))))))"

This works, however for some reason the formula copies all the way down yet it leaves the last cell blank. I can't figure out why it's working, but leaving that last cell empty. Is there something I'm missing to make it fill all the way down?

I'm new to VBA so I appreciate any help.

Thanks,

Jason Z

 

[VoG]

Hello and welcome to MrExcel.

Try

LR = Range("P" & Rows.Count).End(xlUp).Row

 

[Richard Schollar]

Hi Jason

You should base how far your formula needs to go down based on some other column (eg column L maybe?):

Dim LR As Long
LR = Cells(Rows.Count,"L").End(xlUp).Row
Range("P2:P" & LR).FormulaR1C1 = "=IF(AND(RC[-4]>-1,RC[-4]<0.1),""Lowest CTR"",IF(AND(RC[-4]>0.1,RC[-4]<0.2),""Extremely Low"",IF(AND(RC[-4]>0.2,RC[-4]<0.3),""Very Low"",IF(AND(RC[-4]>0.3,RC[-4]<0.4),""Low"",IF(AND(RC[-4]>0.4,RC[-4]<0.6),""Below Average"",IF(RC[-4]=0.1,""Lowest"",IF(RC[-4]=0.2,""Extremely Low"",IF(RC[-4]=0.3,""Very Low"",IF(RC[-4]=0.4,""Low"",IF(OR(RC[-4]=0.5,RC[-4]=0.6),""Below Average"",""Error""))))))))))"

 

[zollan23]

Thanks for the replies everyone. I actually tried this and it worked perfectly!

Range("P2").FormulaR1C1 = "=IF(AND(RC[-4]>-1,RC[-4]<0.1),""Lowest CTR"",IF(AND(RC[-4]>0.1,RC[-4]<0.2),""Extremely Low"",IF(AND(RC[-4]>0.2,RC[-4]<0.3),""Very Low"",IF(AND(RC[-4]>0.3,RC[-4]<0.4),""Low"",IF(AND(RC[-4]>0.4,RC[-4]<0.6),""Below Average"",IF(RC[-4]=0.1,""Lowest"",IF(RC[-4]=0.2,""Extremely Low"",IF(RC[-4]=0.3,""Very Low"",IF(RC[-4]=0.4,""Low"",IF(OR(RC[-4]=0.5,RC[-4]=0.6),""Below Average"",""Error""))))))))))"
    Range("P2").AutoFill Destination:=Range("P2:P" & Range("O" & Rows.Count).End(xlUp).Row)

thanks again