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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas
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 "GettingThere" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas
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. "Tom Ogilvy" wrote: 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 "GettingThere" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas
Other than doing a copy and paste, that seems OK.
-- Regards, Tom Ogilvy "GettingThere" wrote in message ... 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 |
Reply |
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) |