CODEWITHSUNDEEP
vbaexcel
Ross
Ross

Reputation: 147

MonthName(month(now) + x) ..... error on 13; easy fix?

Once month(now) + x => 13 I get an error using MonthName in VBA.

Without building out too much more code is there an easy workaround I'm missing? Only thing I can think of is to set some conditionals inside the loop to adjust it so the month(now) + x reverts back to 1 once it hits 13.

Also looks like I'll have a problem with Year(Now) too once it gets past 13 but one thing at a time.

Code:

Sub Do_Stuff_Button()

expand = 2

MsgBox Day(Now)

ActiveSheet.Cells(2, 6) = "'" & MonthName(Month(Now), False)
ActiveSheet.Cells(1, 6) = "'" & Year(Now)

Do While expand > 0
ActiveSheet.Cells(1, (6 - expand)) = "'" & Year(Now)
ActiveSheet.Cells(1, (6 + expand)) = "'" & Year(Now)
ActiveSheet.Cells(2, (6 - expand)) = "'" & MonthName((Month(Now) - expand), False)
ActiveSheet.Cells(2, (6 + expand)) = "'" & MonthName((Month(Now) + expand), False)
expand = expand - 1
Loop

End Sub

Upvotes: 2

Views: 577

Answers (3)

rheitzman
rheitzman

Reputation: 2297

You can use Format for MonthName:

Debug.Print Format(Now(), "mmmm yyyy")
Debug.Print Format(DateAdd("m", 1, Now()), "mmmm yyyy")

Deals with year rollover as well as month roll over

Upvotes: 0

luke_t
luke_t

Reputation: 2985

You can use DateAdd.

Debug.Print MonthName(Month(DateAdd("m", 1, Date)))

If you were to pass a number into the function, which is greater than the number of remaining months in the current year, then it will calculate the month continuing into the following year.

Example

Debug.Print MonthName(Month(DateAdd("m", 5, Date)))

The above will return March.

Upvotes: 3

Sorceri
Sorceri

Reputation: 8033

you can use the Mod operator to check if there is any value remaining and then pass that as the month value

Dim monthVal As Integer

If (Month(Now) + x) Mod 12 > 0 Then
    monthVal = (Month(Now) + x) Mod 12
Else
    monthVal = Month(Now) + x
End If

MonthName (monthVal)

Upvotes: 3

Related Questions