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

 

[igold]

Hi ExcelDoer, welcome to the MrExcel Forum. If the begging of your autofil range is your "Selection" and the end of your range the "x" range, 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)
    Set sel = Selection
    strt = sel.Address(False, False)
    finsh = x.Address(False, False)
    Selection.AutoFill Destination:=Range(strt & ":" & finsh), Type:=xlFillDefault
    
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I am given a month from the user

Where?

I am given how many months

Where?

 

[ExcelDoer]

Welcome to the MrExcel board!


Where?


Where?

Hello! Both inputs from the user are taken from cells within the worksheet. So they give me the starting month (January for example), and how many months out they want to go out to.

 

[igold]

I can understand why you would want to answer @Peter_SSs questions with his impressive credentials. I don't understand why you would not acknowledge my proposed solution, whether it worked or not.

 

[ExcelDoer]

Thanks for the help igold, I appreciate it. I am still getting the same error though after I made some adjustments. This is what I did:

Dim x As Range, sel As Range
Dim strt As String, finsh As String
Set x = Cells(23, numberOfMonths)
Set sel = Worksheets("Program Forecasting Details").Range("C23")
strt = sel.Address(False, False)
finsh = x.Address(False, False)
Selection.AutoFill Destination:=Range(strt & ":" & finsh), Type:=xlFillDefault

I changed "sel" to the range at which I would like the autofill to start. After running the macro, I got the same error of "Autofill Method of Range class Failed." Do you see where I went wrong? Thanks for the help again.

Hi ExcelDoer, welcome to the MrExcel Forum. If the begging of your autofil range is your "Selection" and the end of your range the "x" range, 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)
    Set sel = Selection
    strt = sel.Address(False, False)
    finsh = x.Address(False, False)
    Selection.AutoFill Destination:=Range(strt & ":" & finsh), Type:=xlFillDefault
  
End Sub

 

[igold]

When Peter had asked "Where" and "Where", he was really asking for which cells specifically on the worksheet. Without knowing the actual cell addresses any solution such as mine is just throwing darts. If you could provide the actual cell addresses, such as where the autofil range will start and where it will end, then a solution should be pretty easy. I understand the user supplies some info but you have enter that info in a certain cells on the sheet. That is the key piece of information that is lacking.

 

[ExcelDoer]

When Peter had asked "Where" and "Where", he was really asking for which cells specifically on the worksheet. Without knowing the actual cell addresses any solution such as mine is just throwing darts. If you could provide the actual cell addresses, such as where the autofil range will start and where it will end, then a solution should be pretty easy. I understand the user supplies some info but you have enter that info in a certain cells on the sheet. That is the key piece of information that is lacking.

The user enters the starting month in cell "B8", and the number of months they want to go out to in "B9".

Then, later on in my macro, I copy and paste the month that the user wants to start at to cell "C23" (supplied from the month given in "B8"). It is from "C23" where I want to autofill months out to the number of months they supplied from "B9". Does that make sense?

 

[igold]

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.

 

[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.

Yep, starting the autofill range at "C23", then I want to go to the right of C23 (D23, F23, E23, etc.) for one month per cell until I hit the number of months the user wanted the autofill function to go out to.

I feel like the solution you provided before should've worked but for some reason the autofill function isn't recognizing it. Maybe because "x" is used by the Cells() function and not Range()?