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

328iXdrive

New Member
Joined
Sep 30, 2016
Messages
3
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!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
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.

Rich (BB code):
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.

Rich (BB code):
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.
 

Forum statistics

Threads
1,082,380
Messages
5,365,117
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top