Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rolling over Month & Year
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rolling over Month & Year
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rolling over Month & Year
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" wrote in message ... 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 Date Const strYear2 As Integer = 2004 strcmonth = Range("C3").Value 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 = strnewmonth End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rolling over Month & Year
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
Rolling Year | Excel Discussion (Misc queries) | |||
Rolling year | Excel Discussion (Misc queries) | |||
How do I set up 12-month rolling month cells in excel? | Excel Discussion (Misc queries) | |||
Rolling Year in Excel | Excel Discussion (Misc queries) |