Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formulas that repeat | Excel Worksheet Functions | |||
How do I repeat an item in multiple formulas w/out retyping each | Excel Worksheet Functions | |||
How do I repeat formulas i.e. m2:x2 then m3:x3 without typing | Excel Discussion (Misc queries) | |||
Macro to repeat formulas in next row | Excel Discussion (Misc queries) | |||
How to program an excel macro to repeat a series of keystrokes? | Excel Discussion (Misc queries) |