Copying formula in variable length column

[awowen]

I need to copy a formula in a variable length column via a macro.
I am using the following:
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.AutoFill Destination:=Range("I2:I6264"), Type:=xlFillDefault
Range("I2:I6264").Select
Obviously, this is not acceptable for variable lengths.
What can I do to accomplish?
Thanks,

 

[Al Chara]

Try the following code:

Rowcounter = Intersect(ActiveSheet.UsedRange, Columns("I")).Rows.Count
For i = 2 To Rowcounter
Range("I" & i).FormulaR1C1 = "=RC[-1]-RC[-2]"
Next

The code places your formula in all cells from I2 to the end of the column.

 

[Juan Pablo González]

On 2002-03-19 12:25, Al Chara wrote:
Try the following code:

Rowcounter = Intersect(ActiveSheet.UsedRange, Columns("I")).Rows.Count
For i = 2 To Rowcounter
Range("I" & i).FormulaR1C1 = "=RC[-1]-RC[-2]"
Next

The code places your formula in all cells from I2 to the end of the column.

Ouch... that loop just hurt me ! :wink:

Try this one:

Range("I2").Resize(Rowcounter,1).FormulaR1C1 = "=RC[-1]-RC[-2]"

 

[Tom Urtis]

Just getting in on the act, this might work also, and it will handle non-contiguous data in column H (which you could adjust for G which if you do, change the offset column reference from 1 to 2.).

Sub FormulaFun()
Range([H2], [H65536].End(xlUp)).Offset(0, 1).Formula = "=RC[-1]-RC[-2]"
End Sub