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