PDA

View Full Version : Solved: VBA to calculate last month



jsfon
07-27-2011, 05:23 AM
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
07-27-2011, 05:58 AM
Hi,

Came up with one solution:

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

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

Kenneth Hobs
07-27-2011, 06:27 AM
Edate needs the analysis toolpak for 2003-.

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

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

jsfon
07-27-2011, 08:43 AM
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
07-27-2011, 04:27 PM
another way, use Excel's EOMONTH function

format(application.worksheetfunction.eomonth(date(),-1),"MMMM")

CatDaddy
07-28-2011, 11:56 AM
Format(Month(Now)-1, "mmmm")