Get name of previous month

[smiler44]

Spent hours on this.

I need to use code and not a formula to enter the name of the previous month into a cell.
I can get the current month by custom formatting mmmm and using =now() but this gives current month and is a formula. I may be able to combine the use of a formula and macro but I dont know.

I can get the number of the previous month by using

Private Sub CommandButton1_Click()
Dim LMonth As Integer
LMonth = Month(Now)
Range("b4").Select
Range("b4") = LMonth - 1
End Sub

I can not workout how to convert the number into the name of the month.

Bet someone here can tell me!


smiler44

 

[pbaldy]

I'd use DateAdd() to subtract a month, and the Format() function to format it as the name of the month. I think both are available in Excel VBA.

 

[smiler44]

Paul,
tried what you said but everything I found on Google failed for DateAdd.

I eventually after many attempts and visits to various web pages managed to construct

[FONT=Arial][SIZE=2][/SIZE][/FONT] 
[FONT=Arial][SIZE=2]Private Sub CommandButton2_Click()
Dim dddd As String[/SIZE][/FONT]
 
[FONT=Arial][SIZE=2]dddd = "=DATE(YEAR(NOW()), MONTH(NOW())-1, DAY(NOW()))"[/SIZE][/FONT]
 
[FONT=Arial][SIZE=2]Range("a7") = dddd[/SIZE][/FONT]
 
[FONT=Arial][SIZE=2]End Sub[/SIZE][/FONT]
[FONT=Arial][/FONT] 
[FONT=Arial]

[/FONT]


The target cell in this case A7 needs to be formatted mmmm. The great thing about this is that it can be used as a formula or macro and I need it as a macro.

smiler44

 

[Mike375]

Range("A7") = MonthName((Month(Date)) - 1)

will enter March

The MonthName((Month(Date)) - 1) is what I use in an Access query and so decided to try it as an Excel macro.

 

[smiler44]

I thought I'd done so well with my method. Not only have you done it in one line of code but you have also done away with the need to format the cell. Much better!

smiler44

 

[smiler44]

Mike375,
I've got a problem that I'm hoping you can solve.
If I use your formula with a command button in sheet1 and the formula goes into sheet1 it works fine.
If I put the code to a command button that is in sheet1 and make the formula go into sheet2 it errors, #name!
If I try and paste = MonthName((Month(Date)) - 1) directly into a cell in sheet1 it also errors #name!

can you help?

Smiler44

 

[smiler44]

Holding head in frustration. Looks like I was using the wrong code to try and put the formula into another sheet. I was using

[SIZE=3][FONT=Times New Roman]Sheets("sheet2").Activate[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Sheets("sheet2").Range("E1").Select[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Sheets("sheet2").Range("E1") = "= MonthName((Month(Date)) - 1)"[/FONT][/SIZE]

I think I should just have used

[FONT=Times New Roman][SIZE=3]Sheets("sheet2").Range("A1") = MonthName((Month(Date)) - 1)[/SIZE][/FONT]
[/code
 
smiler44

 

[Mike375]

I am not an Excel person and so don't know how to reference another sheet.

I just tried it as a formula and it did not work.

Perhaps the problem using it as a formula in a cell is because the cell needs things specific to Excel where as the Excel macro is using VBA.

For example, Access can use IIF but an Excel cell formula needs IF.

 

[Brianwarnock]

I just tried it as a formula and it did not work.

Perhaps the problem using it as a formula in a cell is because the cell needs things specific to Excel where as the Excel macro is using VBA.

.

Yes, MonthName is not a Worksheet Function.

Smiler why are you using 2 lots of ()
MonthName((Month(Date)) - 1)
Just
MonthName(Month(Date) - 1)
Is all that is required and will be less confusing in the future

As you found this

Sheets("sheet2").Activate
Sheets("sheet2").Range("E1").Select
Sheets("sheet2").Range("E1") = "= MonthName((Month(Date)) - 1)"

Places the formula in the cell, which will give a #name error, changing the last line to

Sheets("sheet2").Range("E1") = MonthName((Month(Date)) - 1)

would have worked , but is far less efficient than the code that you eventualy used.

Brian