Repeat series of formulas

[Guest]

Can anyone suggest a more elegant way of repeating a series of formulas than
the following? The most obvious problem (to me) is that if I have an
unexpected number of rows, I would run the formula past the used range.

I would also prefer not to use R1C1 notation, but when I tried I got
absolute cell references.

Thanks!

Sub tester()

' WORKS, BUT MAY ADD FORMULA PAST LAST ROW

With ActiveSheet
lstrow = Range("a" & Rows.Count).End(xlUp).Row
lstrow = lstrow - 2
Debug.Print lstrow
End With

Formula1 = "=SUM(RC[-3]:RC[-2])"
Formula2 = "=RC[-3]*RC[-2]"
Formula3 = "=RC[-3]/RC[-2]"

For i = 1 To lstrow Step 3
Cells(i, 4).FormulaR1C1 = Formula1
Cells(i + 1, 4).FormulaR1C1 = Formula2
Cells(i + 2, 4).FormulaR1C1 = Formula3
Next i
End Sub

 

[Tom Ogilvy]

Do you have formulas in column A that return a null string (""). Or do you
have a null string in some of your cells in column A that cause
lstrow = Range("a" & Rows.Count).End(xlUp).Row

to return an improper row number? How do you define the lastrow if you
look at the sheet.

 

[Guest]

Sorry - I was wrong. It was going past the used range, but that was
obviously before I changed lstrow to lstrow - 2. I really should have
checked more carefully.

So that part is okay - and to answer your question, there is only text in
column A. There should be no null strings, formulas, etc.

I value your opinion though, and would like to know if there is a "better"
way to repeat these formulas through the range.

Thanks Tom.

Do you have formulas in column A that return a null string (""). Or do you
have a null string in some of your cells in column A that cause
lstrow = Range("a" & Rows.Count).End(xlUp).Row

to return an improper row number? How do you define the lastrow if you
look at the sheet.

--
Regards,
Tom Ogilvy

Can anyone suggest a more elegant way of repeating a series of formulas than
the following? The most obvious problem (to me) is that if I have an
unexpected number of rows, I would run the formula past the used range.

I would also prefer not to use R1C1 notation, but when I tried I got
absolute cell references.

Thanks!

Sub tester()

' WORKS, BUT MAY ADD FORMULA PAST LAST ROW

With ActiveSheet
lstrow = Range("a" & Rows.Count).End(xlUp).Row
lstrow = lstrow - 2
Debug.Print lstrow
End With

Formula1 = "=SUM(RC[-3]:RC[-2])"
Formula2 = "=RC[-3]*RC[-2]"
Formula3 = "=RC[-3]/RC[-2]"

For i = 1 To lstrow Step 3
Cells(i, 4).FormulaR1C1 = Formula1
Cells(i + 1, 4).FormulaR1C1 = Formula2
Cells(i + 2, 4).FormulaR1C1 = Formula3
Next i
End Sub

 

[Tom Ogilvy]

Other than doing a copy and paste, that seems OK.