How do you use AutoFill for different ranges? 1

[RooSXL]

The macro below creates the formulas in the rows available and then fills the cell below with the same formulas to the exact range of cells specified when recording. However I have other applications or worksheets where the number of rows could be variables. How do I make run on variable ranges?

Sub FormulasInCells()
Range("I2").Select
ActiveCell.FormulaR1C1 = "=+R[-1]C+RC[-6]"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=+IF(RC[-7]>=0,RC[-7],0)"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=+IF(RC[-8]<0,RC[-8],0)"
Range("I2").Select

Range("I2:K2").Select
Selection.AutoFill Destination:=Range("I2:K53")
Range("I2:K53").Select
End Sub

I would appreciate anybody's insight. Thanks.

 

[Molby]

RooSXL,

Firstly I would re-write parts of your code to avoid selecting cells, somthing like

Range("I2").FormulaR1C1 = "=+R[-1]C+RC[-6]"

As to the autofill, you need to find a way to find out where your range ends and then apply that value to the autofill, something like:

a$ = Range("k65536").End(xlUp).Row
Selection.AutoFill Destination:=Range("I2:K" & ro$)

 

[RooSXL]

This is awesome. Thanks a lot. I am learning a lot from everybody. I hope someday to contribute as much as you do!!