Solved: VBA to calculate last month

[jsfon]

Hi,

I want some code to insert last months name into a cell.

I'm using this code:
WS.Range("G2") = MonthName(Month(Date) - 1)

But this doesn't work if the current month is january or if I put something like: WS.Range("G2") = MonthName(Month(Date) - 8).

Is there an alternative way?

Thanks.

[jsfon]

Hi,

Came up with one solution:

[VBA]On Error Resume Next
Select Case Month(Date)
Case 7 To 12
WS.Range("G2") = MonthName(Month(Date) - 1)
WS.Range("F2") = MonthName(Month(Date) - 2)
WS.Range("E2") = MonthName(Month(Date) - 3)
WS.Range("D2") = MonthName(Month(Date) - 4)
WS.Range("C2") = MonthName(Month(Date) - 5)
WS.Range("B2") = MonthName(Month(Date) - 6)
Case 6
WS.Range("G2") = MonthName(Month(Date) - 1)
WS.Range("F2") = MonthName(Month(Date) - 2)
WS.Range("E2") = MonthName(Month(Date) - 3)
WS.Range("D2") = MonthName(Month(Date) - 4)
WS.Range("C2") = MonthName(Month(Date) - 5)
WS.Range("B2") = "December"
Case 5
WS.Range("G2") = MonthName(Month(Date) - 1)
WS.Range("F2") = MonthName(Month(Date) - 2)
WS.Range("E2") = MonthName(Month(Date) - 3)
WS.Range("D2") = MonthName(Month(Date) - 4)
WS.Range("C2") = "December"
WS.Range("B2") = "November"
Case 4
WS.Range("G2") = MonthName(Month(Date) - 1)
WS.Range("F2") = MonthName(Month(Date) - 2)
WS.Range("E2") = MonthName(Month(Date) - 3)
WS.Range("D2") = "December"
WS.Range("C2") = "November"
WS.Range("B2") = "October"
Case 3
WS.Range("G2") = MonthName(Month(Date) - 1)
WS.Range("F2") = MonthName(Month(Date) - 2)
WS.Range("E2") = "December"
WS.Range("D2") = "November"
WS.Range("C2") = "October"
WS.Range("B2") = "September"
Case 2
WS.Range("G2") = MonthName(Month(Date) - 1)
WS.Range("F2") = "December"
WS.Range("E2") = "November"
WS.Range("D2") = "October"
WS.Range("C2") = "September"
WS.Range("B2") = "August"
Case 1
WS.Range("G2") = "December"
WS.Range("F2") = "November"
WS.Range("E2") = "October"
WS.Range("D2") = "September"
WS.Range("C2") = "August"
WS.Range("B2") = "July"
End Select[/VBA]

But if there is a better way, please let me know.
Regards

[Kenneth Hobs]

Edate needs the analysis toolpak for 2003-.

=TEXT(EDATE(TODAY(),-1),"mmmm")

[shrivallabha]

And there is one more way:
=TEXT(TODAY()-DAY(TODAY()),"mmm")
This takes you to the last day of previous month...!

[jsfon]

Thank you for the help.

There's also another solution. It suits me better because this will be running in Excel 2003 and I also need it to calculate last month -1,-2,-3...

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-6,0),"MMMM")

Again thanks for all the solutions.
Regards J.

[Shred Dude]

another way, use Excel's EOMONTH function

[VBA]format(application.worksheetfunction.eomonth(date(),-1),"MMMM")[/VBA]

[CatDaddy]

[VBA]Format(Month(Now)-1, "mmmm")[/VBA]