Create variable month list in a column.

[mopey12345]

Hi everyone,
I have to create a list of months 6 (min) up to 12 (max) using any month as the start month. Two cells define the start month and the number of months. Is there a better way than creating 84 arrays to cover all instances? eg JANMONTHNAMES6 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun""), upto JANMONTHNAMES12= Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), for each month. Thanks in advance.

 

[jasonb75]

When you first start it's often easy to overthink tasks and some of the help guides that you find are not always helpful.

See if this does the trick

Sub test()
Range("D3").Value = Range("C3").Value
Range("D3").AutoFill Destination:=Range("D3").Resize(, Range("C4").Value), Type:=xlFillDefault
End Sub

 

[mopey12345]

When you first start it's often easy to overthink tasks and some of the help guides that you find are not always helpful.

See if this does the trick

Sub test()
Range("D3").Value = Range("C3").Value
Range("D3").AutoFill Destination:=Range("D3").Resize(, Range("C4").Value), Type:=xlFillDefault
End Sub

Thanks so much for helping, exactly right about over complicating. Regards Phil

 

[mopey12345]

Thanks so much for helping, exactly right about over complicating. Regards Phil

Hi
Sorry to ask again but hit another snag. Getting a run time error 1004 , application or object not defined on autofill line.
Sub test()
With Worksheets("Sheet2")
Range("D3").Value = Range("C3").Value

Range("D3").AutoFill Destination:=Range("D3").Resize(, Range("C4").Value), Type:=xlFillDefault
End With
End Sub

 

[Rick Rothstein]

Put a dot in front of each Range object to they point to the worksheet defined by the With statement... without the dot, those ranges default to the active worksheet.

Note: Since your example code showed you want the month names down Column D, you need to remove the comma inside the Resize property's argument (with the comma, the months would be listed across Row 3 instead).

Here is the code with the revisions I suggested...

Sub test()
  With Worksheets("Sheet2")
    .Range("D3").Value = .Range("C3").Value
    .Range("D3").AutoFill Destination:=.Range("D3").Resize(.Range("C4").Value), Type:=xlFillDefault
  End With
End Sub

Just pointing out that the above code is more verbose than it needs to be. True, it is probably more understandable especially if you are new to VBA, but so you know, the following is equivalent to the modified code I posted above...

Sub test()
  With Worksheets("Sheet2")
    .[D3] = .[C3]
    .[D3].AutoFill .[D3].Resize(.[C4])
  End With
End Sub

 

[jasonb75]

I was just typing up a reply but Rick beat me to it so no point in duplicating the same reply.

Thanks for picking up on the resize error, Rick

 

[mopey12345]

I was just typing up a reply but Rick beat me to it so no point in duplicating the same reply.

Thanks for picking up on the resize error, Rick

Put a dot in front of each Range object to they point to the worksheet defined by the With statement... without the dot, those ranges default to the active worksheet.

Note: Since your example code showed you want the month names down Column D, you need to remove the comma inside the Resize property's argument (with the comma, the months would be listed across Row 3 instead).

Here is the code with the revisions I suggested...

Sub test()
  With Worksheets("Sheet2")
    .Range("D3").Value = .Range("C3").Value
    .Range("D3").AutoFill Destination:=.Range("D3").Resize(.Range("C4").Value), Type:=xlFillDefault
  End With
End Sub

Just pointing out that the above code is more verbose than it needs to be. True, it is probably more understandable especially if you are new to VBA, but so you know, the following is equivalent to the modified code I posted above...

Sub test()
  With Worksheets("Sheet2")
    .[D3] = .[C3]
    .[D3].AutoFill .[D3].Resize(.[C4])
  End With
End Sub

Thank you for helping. Much appreciated as it would take me ages to figure out, if at all. Regards Phil

 

[mopey12345]

I was just typing up a reply but Rick beat me to it so no point in duplicating the same reply.

Thanks for picking up on the resize error, Rick

Thanks again for all your help. Much appreciated. regards Phil