VBA to List Months Between Two Years

[angusfire]

I am trying to work out a VBA Sub that will list out all the months between 2 given years. For example; year range is A1:B2 and the Sub would list out the values as shown in A3:B__

[TABLE="width: 106"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1940[/TD]
[TD="align: center"]2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1940[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: center"]1940[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD="align: center"]1941[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD="align: center"]1942[/TD]
[TD="align: center"]12[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!!

Angusfire

 

[Fluff]

How about

Sub angusfire()
   Dim i As Long, Rw As Long
   Rw = 3
   For i = Range("A1") To Range("B1")
      Range("A" & Rw).Resize(12).Value = i
      Range("B" & Rw) = 1
      Range("B" & Rw).AutoFill Range("B" & Rw).Resize(12), xlFillSeries
      Rw = Rw + 12
   Next i
End Sub

 

[Joe4]

Try this code:

Sub MyPopulator()

    Dim startyear As Long, endyear As Long
    Dim y As Long, m As Long
    Dim r As Long
    
    On Error GoTo err_check

'   Capture years
    startyear = Range("A1")
    endyear = Range("B1")
    
    On Error GoTo 0
    
'   Make sure that start year is after 1900
    If startyear < 1900 Then
        MsgBox "Start Year in cell A1 must be after 1900", vbOKOnly, "ENTRY ERROR!"
        Exit Sub
    End If

'   Make sure that end year is not before start year
    If endyear < startyear Then
        MsgBox "End Year in cell B1 cannot be before Start Year in cell A1", vbOKOnly, "ENTRY ERROR!"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
'   Set starting row number
    r = 3
    
'   Loop through all years
    For y = startyear To endyear
'       Loop through all months
        For m = 1 To 12
            Cells(r, "A") = y
            Cells(r, "B") = m
            r = r + 1
        Next m
    Next y
        
    Application.ScreenUpdating = True

    Exit Sub
    

err_check:
    If Err.Number = 13 Then
        MsgBox "Entries in cells A1 and B1 must be numbers!", vbOKOnly, "ENTRY ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

I got a few validations in there to help ensure they are entering valid values.