CODEWITHSUNDEEP
vbaexcel
Meesha
Meesha

Reputation: 821

Generating sequence of Dates

I have written a code which gives me a sequence of dates in the form of "mmm yy" format. e.g. Jan 15 .

For i = 1 To 20
MsgBox Format(DateAdd("m", i, "01/01/2005"), "mmm") & " " & Right(Year(Date), 2)
Next i

The problem I am having is that when i goes on increasing the year does not change. So for example if i is 12 then it should be Jan 06 but still it gives me Jan 05 again. I want to generate a sequence from Jan 05 to Today i.e. Sep 15 Please advice.

Upvotes: 1

Views: 75

Answers (2)

MatthewD
MatthewD

Reputation: 6761

Create a variable and add to that date.

Private Sub CommandButton1_Click()
    Dim dDate As Date
    Dim i As Integer

    dDate = "01/01/2005"

    For i = 1 To 20
        MsgBox Format(dDate, "mmm") & " " & Right(Year(dDate), 2)
        dDate = DateAdd("m", 1, dDate)
    Next i

End Sub

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96773

Consider:

Sub oiudskfh()
   For i = 1 To 20
      Dim d As Date
      d = DateAdd("m", i, "01/01/2015")
      MsgBox Format(d, "mmm") & " " & Right(d, 2)
   Next i
End Sub

Upvotes: 2

Related Questions