CODEWITHSUNDEEP
excelvba
Darren
Darren

Reputation: 9

Creating a string of month in a column

I would like to create a date column formatted via "mmm-yyyy" but starting with next year's date, i.e.

Jan-2020
Feb-2020
Mar-2020
Apr-2020
May-2020
Jun-2020
Jul-2020
Aug-2020
Sep-2020
Oct-2020
Nov-2020
Dec-2020

My code only created the same month for 12 times. Can any one help me with this?

My current code

Sub demo()

'month recurring till dec
Dim x As Integer
Dim i As Integer

For x = 1 To 12

    For i = 1 To 12
        StartDate = (month(x + 1)) & "-" & (Year(Now())) + 1
        Cells(i, 1).Value = StartDate
        Cells(i, 1).NumberFormat = "mmm-yyyy"
    Next i

Next x

End Sub

Upvotes: 0

Views: 57

Answers (1)

T.M.
T.M.

Reputation: 9948

Write month dates into column

You have several issues here. Basically you aren't incrementing the year (correct: Year(Now) + increment) and you are overwriting each target cell 12-times with the last calculated value.

Working example procedure

Option Explicit                     ' declaration head of your code module

Sub demo()
With Sheet1                         ' << Reference the sheet's CodeName, e.g. Sheet1
    Dim repetition As Long          ' << Long, provide for counters greater than ~65K
    For repetition = 1 To 10        ' << change to ..= 1 to 1 if only one column :-)

        Dim mon As Long
        For mon = 1 To 12
          ' calculate month date via DateSerial function (arguments year,month,day) 
            Dim StartDate As Date
            StartDate = DateSerial(Year(Now) + repetition, mon, 1)
          ' write to fully referenced range - note the prefixed "." referring to the sheet object  
            .Cells((repetition - 1) * 12 + mon, 1).Value = StartDate
            .Cells((repetition - 1) * 12 + mon, 1).NumberFormat = "mmm-yyyy"
        Next mon

    Next repetition
End With
End Sub

Upvotes: 1

Related Questions