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")
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.