Please help! Looping for a product until a row is empty

[328iXdrive]

Hey Folks,

Long time reader, first time poster. I'm trying to have my code enter a formula in T8 (and T9, T10, etc.) until there is no longer a value in cell in row 8. It is based off another format so I need it to start on cell T8 so it doesn't pick up the headers (hence me trying to activate T8 first). However when I try this code, I see the cells being activated and the cursor moving down, however it hasn't copied in my formula to any of the cells:

Sub LossFormula()
'
' LossFormula Macro
'

Range("T8").Select
Do Until IsEmpty(ActiveCell.Offset(, -14))
FormulaR1C1 = "=(RC[-2]/RC[-3])*RC[-1]"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

I know that the formula is right because when I try just putting that in with no loop whatsoever, the correct formula does appear in cell T8, but when I add the loop functions, it hops down but nothing happens. Is there anyone who could please help and let me know what I'm doing wrong?

Thanks in advance!

 

[328iXdrive]

Oops* Meant to say "Until there is no longer a value in column A", not "row 8"

 

[Eric W]

Welcome to the forum.

If you just want to fix up your macro for educational purposes, look at the first piece of code. But you can do the same functionality much quicker using the second piece.

Sub LossFormula()
'
' LossFormula Macro
'
    Range("T8").Select
    Do Until IsEmpty(ActiveCell.Offset(, -19))
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-3])*RC[-1]"
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

You had 2 things that needed changing. The offset to column A is -19, not -14. And you were missing the object for the .FormulaR1C1 property.

Sub LossFormula2()

    Range("T8:T" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=(RC[-2]/RC[-3])*RC[-1]"
End Sub

This version finds the last row using the .End property, and then inserts all the formulas at one time.

Hope this helps.