Auto fill a list with a formula
 

I recorded this macro, and of course it does what it's asked.

'Inserts a formula in column H to calculate Amount
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Selection.AutoFill Destination:=Range("H2:H65000")
Range("A2").Select

To improve this macro, I was hoping to modify it so it only copied down the
list (no matter what size the list is) - not down to row 65,000. How would I
do this?

Auto fill a list with a formula
 

Try using this code instead

Sub test04()
Dim x, i As Integer
x = Range("G1:" & ActiveSheet.Cells(Rows.Count,
"G").End(xlUp).Address).Count
For i = 2 To x Step 1
Cells(i, 8).Formula = "=E" & i & "*G" & i
Next
End Sub

Sandy


Kirk P. wrote:
I recorded this macro, and of course it does what it's asked.

'Inserts a formula in column H to calculate Amount
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Selection.AutoFill Destination:=Range("H2:H65000")
Range("A2").Select

To improve this macro, I was hoping to modify it so it only copied down the
list (no matter what size the list is) - not down to row 65,000. How would I
do this?

Auto fill a list with a formula
 

Try using this code instead

Sub test04()
Dim x, i As Integer
x = Range("G1:" & ActiveSheet.Cells(Rows.Count, "G") _
..End(xlUp).Address).Count
For i = 2 To x Step 1
Cells(i, 8).Formula = "=E" & i & "*G" & i
Next
End Sub

Sandy


Kirk P. wrote:
I recorded this macro, and of course it does what it's asked.

'Inserts a formula in column H to calculate Amount
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Selection.AutoFill Destination:=Range("H2:H65000")
Range("A2").Select

To improve this macro, I was hoping to modify it so it only copied down the
list (no matter what size the list is) - not down to row 65,000. How would I
do this?