Repeat series of formulas - REVISITED

[Guest]

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.

 

[Bob Phillips]

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


--

HTH

RP
(remove nothere from the email address if mailing direct)

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

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

 

[abcd]

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

 

[Guest]

Gosh, it's all so easy when you know what you are doing! Thanks guys.

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