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

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

In article ,
"Ian Ripsher" wrote:

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

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

"JE McGimpsey" wrote in message
...
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

In article ,
"Ian Ripsher" wrote:

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