Rolling over Month & Year

A

Al

Hi All

I'm trying to use a macro that rolls over an abbreviated month & year. I.e. Jan04 to Feb04
The cell the macro refers to is text formatted, and it needs to stay in that format

The problem i have is that when the macro rolls over to a new year it rolls over succesfully to Jan05 but then reverts back to Feb05
Can someone please help in the modify of the below code? I've tried several times but get constant errors

Cheers
Al

------------------
Sub NeDateChange(
Dim dtval As Dat
Const strYear2 As Integer = 200
strcmonth = Range("C3").Valu
strb2month = Left(strcmonth, 3
strYear = "20" & Right(strcmonth, 2
dtval = DateValue(strb2month & " 1," & strYear
strb3month = Format(DateSerial(strYear2, Month(dtval) + 1, 1), "mmmyy"
strnewmonth = Application.Substitute(strcmonth, Left(strcmonth, 5), strb3month
Range("C3").Value = strnewmont
End Su
 
A

Al

Forgot to mention
Yes i know i have a constant year for 2004 in the coding
I've tried to take that out and change it to a rolloing year, but i couldn't
Cheers
Al
 
R

Rob van Gelder

Looks like you're close. You don't need strYear2
In the DateSerial function use strYear instead

I must ask, why don't you store the date as a date instead of text? You
could apply a custom format to the cell. eg. mmmyy

The code then is straight forward:
Sub test()
Dim dtm As Date
dtm = Range("C3").Value
Range("C3").Value = DateSerial(Year(dtm), Month(dtm) + 1, 1)
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


Al said:
Hi All,

I'm trying to use a macro that rolls over an abbreviated month & year. I.e. Jan04 to Feb04.
The cell the macro refers to is text formatted, and it needs to stay in that format.

The problem i have is that when the macro rolls over to a new year it
Click to expand...
rolls over succesfully to Jan05 but then reverts back to Feb05.
Can someone please help in the modify of the below code? I've tried
Click to expand...
several times but get constant errors.
 
A

Al

Thanks for your help Rob

To answer your question. I suppose i could apply a custom format after changing the date, but i'd be concerned that other formula's and macro's that will be reading from the updated dates (there will be around 8 that will be updated monthy) will then not funtion properly

Just a bit of "fear factor" involved really, and keeping the format as text helps alleviate that
But i think now, that might be another avenue i can explore, being that i'm a novice and all

Thanks again for your help. Greatly appreciated
Cheers
Al.
 
Top