Formatting Dates in VBA problem (Excel 2003)

Question

I am having a small problem with automating the insertion of dates into a workbook based on the current date the macro is run. What I am trying to do is add the months for the next 4 years in a column header (without including the months that have already passed for the first year).

For example, if I run the macro today (28/8/2012) it should produce column headers starting from September 2012 up to December 2015. The way I want the dates to be formatted is sept-12 or something similar to that.

The following is the piece of VBA code I use in the macro to accomplish this:

Dim currentmonth As String

currentmonth = Month(Now)

Dim LDate As String

LDate = Date

For y = 1 To 48 - CInt(currentmonth)

        scheduleWb.Sheets("Template").Cells(1, x).Value = Format(DateAdd("m", y, LDate), "mmm-yyyy")

        x = x + 1

Next y

Now the problem is for the months of September to November the value in the cell is numerical like 2012-09-01 but for the rest of the months the value in the cell is a string like déc-2012.

For December and onwards it seems to take the mmm-yyyy formatting and applying it directly to the cell’s value. While in the end I get the months I want it creates problems when I want to do further work on these months. So when I try to parse only the year of the date I need to use Right() or Left() depending if the cell’s value is numerical or not.

While I did manage to find a work around for parsing the dates, it is bugging me why this would occur.

Here is an example of what it might look like:

sept-12                      oct-12                  nov-12              déc-12                      janv-13                 févr-13                mars-13

2012-09-01               2012-10-01       2012-11-01      2012-12-01             janv-2013             févr-2013           mars-2013

Top row is how the cell is displayed in Excel. Bottom row is the actual value you will find in the cell.

By the way I am using both French Office 2003 and Windows XP.

Answer 1

Try this:

    Dim m As Long

    Dim x As Long

    m = 48 - Month(Date)

    Range(Cells(1, 1), Cells(1, m)).NumberFormat = "mmm-yyyy"

    For x = 1 To m

        scheduleWb.Sheets("Template").Cells(1, x).Value = _

            DateSerial(Year(Date), Month(Date) + x, 1)

    Next x

Or if you want the cells to contain text values:

    Dim m As Long

    Dim x As Long

    m = 48 - Month(Date)

    Range(Cells(1, 1), Cells(1, m)).NumberFormat = "@"

    For x = 1 To m

        scheduleWb.Sheets("Template").Cells(1, x).Value = _

            Format(DateSerial(Year(Date), Month(Date) + x, 1), "mmm-yyyy")

    Next x

Answer 2

Thanks your first solution is what I was looking for.

I replaced DateAdd with DateSerial in my original code and the error continued. Then I used your code and it was working correctly. I am guessing I was not using Format() properly and should have used NumberFormat on the cell instead.