How to drag autofill from a set location a variable amount of cells away

[ExcelDoer]

Hello everyone! I am hoping someone has figured out this situation as I am having trouble with it.

The Problem:

• I am given a month from the user
• I am given how many months out the user wants to autofill from the given month
  • Example: The given month is January, and they want to autofill out 6 months to June

How do I write the autofill function within VBA to include the dynamic column length to which I want to autofill the formula? This is what I have right now but I get errors from it:

dim x as range
dim numberOfMonths as integer

numberOfMonths = 15
set x = cells(23,numberOfMonths)
Selection.AutoFill Destination:=Range(x), Type:=xlFillDefault

 

[ExcelDoer]

So you are starting your autofil range @ cell "C23", are you then going down Column C to the number months (I assume one cell per month) or are you going across row 23 for the ending cell in your range.

Here's a picture of the situation I have within my worksheet if it helps:

I would just like the macro to start from the cell that has January, and go out 8 months through the autofill drag method.

 

[igold]

If C23 is always your start point then how about this...

Sub autofil()

    Dim x As Range, sel As Range
    Dim numberOfMonths As Integer
    Dim strt As String, finsh As String
    numberOfMonths = 15
    Set x = Cells(23, numberOfMonths + 3)
    Set sel = Range("C23")
    strt = sel.Address(False, False)
    finsh = x.Address(False, False)
    Range("C23").AutoFill Destination:=Range(strt & ":" & finsh), Type:=xlFillDefault
    
End Sub

 

[ExcelDoer]

If C23 is always your start point then how about this...

Sub autofil()

    Dim x As Range, sel As Range
    Dim numberOfMonths As Integer
    Dim strt As String, finsh As String
    numberOfMonths = 15
    Set x = Cells(23, numberOfMonths + 3)
    Set sel = Range("C23")
    strt = sel.Address(False, False)
    finsh = x.Address(False, False)
    Range("C23").AutoFill Destination:=Range(strt & ":" & finsh), Type:=xlFillDefault
   
End Sub

I still get the same error, "Autofill method of Range class failed." My only guess is because within the autofill function, the range we are providing isn't valid for some reason.

 

[ExcelDoer]

I still get the same error, "Autofill method of Range class failed." My only guess is because within the autofill function, the range we are providing isn't valid for some reason.

Never mind I made a typo error, that solved it !!! Thank you so much for the help @igold - I was going crazy trying to figure this out. I really appreciate your help!

 

[igold]

You're welcome. We were all happy to help! Thanks for the feedback!

 

[Peter_SSs]

If you already have "January" (or whatever month) in C23 based on the value in B8 and the number of months is in B9 then this single line should do what you want?

Sub Test()
  Range("C23").AutoFill Destination:=Range("C23").Resize(, Range("B9").Value), Type:=xlFillDefault
End Sub