Macro with dynamic range selection works for large range of rows but doesn't when the range is just 1 row

[patrasgabriela]

Hello,

I have created a Macro that uses a variable count the rows in order to copy paste a formula from Cell 2 of that column on all rows and it works perfectly when the range is made from multiple rows, but doesn't work if the range is just one row. The macro gets stuck at when reading the range. Is there a way to make this work? Below my macro. Thank you in Advance for your help.

Sub workPF()
lRow = Range("A" & Rows.Count).End(xlUp).Row
       
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-10]>0,RC[-8]/(RC[-13]*RC[-9]*RC[-10]),RC[-8]/(RC[-13]*RC[-9]))"
    Range("AI2").AutoFill Destination:=Range("AI2:AI" & lRow), Type:=xlFillDefault

End sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub workPF()
lRow = Range("A" & Rows.Count).End(xlUp).Row
       
    Range("AI2:AI" & lRow).FormulaR1C1 = _
        "=IF(RC[-10]>0,RC[-8]/(RC[-13]*RC[-9]*RC[-10]),RC[-8]/(RC[-13]*RC[-9]))"

End Sub

 

[patrasgabriela]

Hi and thank you for you very prompt reply.

That works perfectly and easier way to write better code using less lines. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.