Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas - REVISITED
Sorry for starting a new thread, but I have a new problem:
How can I make the formulas below always look at column A? I don't know in advance what column I am going to be working with, so I am getting incorrect column references with the following: Sub inputToLastColumnTESTER3() Dim colNum As Integer Dim lstRow As Integer Dim frmRow As Integer ' lst row for formula Dim i As Integer Dim rng As Range Dim newRng As Range Dim Formula1 As String Dim Formula2 As String Dim Formula3 As String Set rng = Cells(1, "IV").End(xlToLeft) ' get last used column 'Debug.Print "Lst column is " & rng.Address Set newRng = rng.Cells(1, 2).Resize(1, 1) 'range to input next - not used yet ' Debug.Print "new range is " & newRng.Address colNum = rng.Column + 1 lstRow = Range("a" & Rows.Count).End(xlUp).Row frmRow = lstRow - 2 ' Debug.Print "column index is " & colNum & " lstRow is " & lstRow Formula1 = "=SUM(RC[-3]:RC[-2])" ' HOW TO MAKE ALWAYS REFER TO COL A ?? Formula2 = "=RC[-3]*RC[-2]" Formula3 = "=RC[-3]/RC[-2]" For i = 1 To frmRow Step 3 Cells(i, colNum).FormulaR1C1 = Formula1 Cells(i + 1, colNum).FormulaR1C1 = Formula2 Cells(i + 2, colNum).FormulaR1C1 = Formula3 Next i End Sub ORIGINAL THREAD FOLLOWS: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas - REVISITED
Formula1 = "=SUM(RC1:RC[-2])"
Formula2 = "=RC1*RC[-2]" Formula3 = "=RC1/RC[-2]" -- HTH RP (remove nothere from the email address if mailing direct) "GettingThere" wrote in message ... Sorry for starting a new thread, but I have a new problem: How can I make the formulas below always look at column A? I don't know in advance what column I am going to be working with, so I am getting incorrect column references with the following: Sub inputToLastColumnTESTER3() Dim colNum As Integer Dim lstRow As Integer Dim frmRow As Integer ' lst row for formula Dim i As Integer Dim rng As Range Dim newRng As Range Dim Formula1 As String Dim Formula2 As String Dim Formula3 As String Set rng = Cells(1, "IV").End(xlToLeft) ' get last used column 'Debug.Print "Lst column is " & rng.Address Set newRng = rng.Cells(1, 2).Resize(1, 1) 'range to input next - not used yet ' Debug.Print "new range is " & newRng.Address colNum = rng.Column + 1 lstRow = Range("a" & Rows.Count).End(xlUp).Row frmRow = lstRow - 2 ' Debug.Print "column index is " & colNum & " lstRow is " & lstRow Formula1 = "=SUM(RC[-3]:RC[-2])" ' HOW TO MAKE ALWAYS REFER TO COL A ?? Formula2 = "=RC[-3]*RC[-2]" Formula3 = "=RC[-3]/RC[-2]" For i = 1 To frmRow Step 3 Cells(i, colNum).FormulaR1C1 = Formula1 Cells(i + 1, colNum).FormulaR1C1 = Formula2 Cells(i + 2, colNum).FormulaR1C1 = Formula3 Next i End Sub ORIGINAL THREAD FOLLOWS: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas - REVISITED
Formula1 = "=SUM(RC[-3]:RC[-2])" ' HOW TO MAKE ALWAYS REFER TO COL A ?? Cells(i, colNum).FormulaR1C1 = Formula1 R[1]C[0] means the next row (relative) in the same column but there's also an absolute wsay with RC style: R1C1 means the cell in column 1 and row 1 (A1 cell) So R[2]C1 means the column 1 (absolute) but 2 rows below (relative) the [] means relative without it means absolute |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat series of formulas - REVISITED
Gosh, it's all so easy when you know what you are doing! Thanks guys.
"abcd" wrote: Formula1 = "=SUM(RC[-3]:RC[-2])" ' HOW TO MAKE ALWAYS REFER TO COL A ?? Cells(i, colNum).FormulaR1C1 = Formula1 R[1]C[0] means the next row (relative) in the same column but there's also an absolute wsay with RC style: R1C1 means the cell in column 1 and row 1 (A1 cell) So R[2]C1 means the column 1 (absolute) but 2 rows below (relative) the [] means relative without it means absolute |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formulas that repeat | Excel Worksheet Functions | |||
How do I repeat formulas i.e. m2:x2 then m3:x3 without typing | Excel Discussion (Misc queries) | |||
Repeat series of formulas | Excel Programming | |||
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) |