Increment Month/Year in "mmm-yy" format

[Ian Ripsher]

I've written a sub to increment the month to the next one, so that Jan-61 ->
Feb-61... Dec-61 -> Jan-62, etc, whenever a date is in the cell (value in
the formula bar shows UK-style date format dd/mm/yyyy, but formatted as
"mmm-yy" in the cells). The code is copied below. What's driving me mad is
that it works for years up to 1999, it then changes Dec-99 to Jan-00 Ok, but
then it changes Dec-00 to Jan-04 and thereafter the year "sticks" at 2004,
although the months cycle round OK. I've used F8 to watch each step, but I
can't fathom what's going wrong. I think it may have something to do with
the day, because for dates up to 2000, the formula bar shows 01/mm/yyyy, but
after that shows 04 or 05. Is this some weird date format thing - or a UK/US
date incompatibility? Very grateful if somebody could tell me where my code
is going wrong.

Sub NextMonth()
Dim UsedArea As Range
Dim c As Range
Dim ThisMonth As Integer
Dim ThisYear As Integer
Dim NextMonth As Integer
Dim NextYear As Integer
Set UsedArea = ThisWorkbook.Worksheets("Test Sheet").UsedRange
For Each c In UsedArea.Cells
If IsDate(c.Value) Then
ThisMonth = Mid(c.Value, 4, 2)
ThisYear = Right(c.Value, 4)
End If

If ThisMonth = 12 Then
NextMonth = 1
NextYear = ThisYear + 1
Else
NextMonth = ThisMonth + 1
NextYear = ThisYear
End If

c.Value = Format(NextMonth & "-" & NextYear, "mmm-yy")

Next c
End Sub

 

[JE McGimpsey]

First, your using Format() returns a string. XL's parser interprets
Jan-05 as 5 January of the current year.

Using Format() has no effect on what is displayed in the cell - that's
determined completely by the number format and how the parser interprets
the input.

Here's one alternative:

Public Sub NextMonth()
Dim c As Range
For Each c In Sheets("Test Sheet").UsedRange
If IsDate(c.Value) Then
With c
.Value = DateSerial(Year(.Value), Month(.Value) + 1, 1)
End With
End If
Next c
End Sub

 

[Ian Ripsher]

Many thanks. As usual, the solution is much easier than I was trying to do
it. I should have twigged the DateSerial function.