Setting up a variable range

[Anguscat]

I'm trying to set up a varable range to pass into the auto fill funtion. The range will vary depending on the number of months. The months start F1 and will vary from 1 to 12. In the example below June is the starting month (in cell F1) and I want to autofill the formula through december.

Range("F4").Select
ActiveCell.FormulaR1C1 = "=R[-2]C-R[-1]C"
Selection.AutoFill Destination:=Range("F4:L4"), Type:=xlFillDefault

Next month July will be in cell F1 and I will need to change the fill range to ("F4:K4")

any ideas?

Thanks,
Bill

 

[brian.wethington]

I am assuming that you will have something in the row above where you will be putting this? For example can you use the last column of row 3 to determine how many columns you need to fill to? I guess I reall would like to see exactly what you are doing and how your data is set up.

 

[Anguscat]

I do have data above that row:

Row 1 06/01/2007 07/01/2007 08/01/2007
Row 2 5000 2000 4000
Row 3 5000 2000 4000
Row 4 0 0 0


Row 4 is what I want to populate with the formula that subtracts row 3 from row 2 to see if the data ties. The starting point will always be the same cell, but the end point will change.

 

[brian.wethington]

Perhaps something like this will do then:

Dim LstCol As Long
LstCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range("F4")
    .FormulaR1C1 = "=R[-2]C-R[-1]C"
    .AutoFill Destination:=Range(Cells(4, 6), Cells(4, LstCol)), Type:=xlFillDefault
End With

 

[Anguscat]

Works perfect.

Thanks for your help!!



Bill