please help me format this date to Month and Year

[Yossy]

I have following code that automatically adjust date every month. The date
format in column is January. I need help in making the date January 2009. My
date gets adjusted every month by the code. So next month will be February.
Currently if I put January 2009 in the column and run the code, I get Error
"Type mismatch" becos the code only recognises Month and not Month and year.
I want the code to recognize Month and Year where I have January 2009 so this
way it will automatically update to February 2009 and so on every month. Thus
After December it should increase the year too.

All Help totally appreciated. Thanks

CurrentMonth = Month(DateValue(c.Offset(1, 0) & _
" 1 " & Year(Date)))
If CurrentMonth = 12 Then
NextMonth = "January"
Else
NextMonth = Format(DateValue((CurrentMonth + 1) & _
"/1/" & Year(Date)), "mmmm")
End If
NewCell.Offset(1, 0).Value = NextMonth

 

[Bob Phillips]

CurrentMonth = Month(DateValue("01 " & c.Offset(1, 0)))
If CurrentMonth = 12 Then
NextMonth = "January"
Else
NextMonth = Format(DateValue((CurrentMonth + 1) & _
"/1/" & Year(Date)), "mmmm")
End If
NewCell.Offset(1, 0).Value = NextMonth

 

[Joel]

'added two line for my testing
Set c = Range("A1")
Set NewCell = Range("B1")


CurrentDate = c.Offset(1, 0)
If Month(CurrentDate) = 12 Then
NextMonth = DateSerial(Year(c) + 1, 1, 1)
Else
NextMonth = DateSerial(Year(CurrentDate), Month(CurrentDate)
+ 1, 1)
End If
NewCell.Offset(1, 0).Value = NextMonth

 

[Rick Rothstein]

Try it this way...

CurrentMonth = Month(DateValue(C.Offset(1, 0) & " 1 " & Year(Date)))
NextMonth = MonthName((CurrentMonth + 1) Mod 12)
NewCell.Offset(1, 0).Value = NextMonth

which, if you don't need the NextMonth variable for anything else, can be
shortened to this and the NextMonth variable can be eliminated...

CurrentMonth = Month(DateValue(C.Offset(1, 0) & " 1 " & Year(Date)))
NewCell.Offset(1, 0).Value = MonthName((CurrentMonth + 1) Mod 12)

 

[Rick Rothstein]

I just re-read the original post... what is in the cell? Text that says
"January 2009" or a real date formatted to display "January 2009"?

If text, I think I would do it with this one-liner...

NewCell.Offset(1, 0).Value = Format(DateAdd("m", 1, CDate("01 " & _
C.Offset(1, 0).Value)), "mmmm yyyy")

If it is a real date, then I think I would use this one-liner instead
(formatting NewCell as appropriate)...

NewCell.Offset(1, 0).Value = DateAdd("m", 1, C.Offset(1, 0).Value)