Macro: MonthName Function, runtime error 424

[cbetmark]

I have a workbook with 2 worksheets named WKO and Report. I am tryin
to write a macro to look at a cell containing a date in worksheet WK
and put the month's name in a cell on worksheet Report. The dat
format of the cell is 2005/07/05. Here is my code.

Sub
Dim j as String

j = MonthName(Month(WKO!C3))

Range("A1").Select
ActiveCell.Formula = "j"

End Sub

I get a 424 Runtime Error - No Object Defined for the line
=MonthName(Month(WKO!C3)

 

[Bernie Deitrick]

cbetmark,

Sub test()
Worksheets("Report").Range("A1").Value = _
Format(Worksheets("WKO").Range("C3").Value, "mmmm")
End Sub

Or you could use a formula in cell A1 of Report:

=WKO!C3

and format cell A1 of Report for custom, mmmm

HTH,
Bernie
MS Excel MVP

 

[cbetmark]

Bernie,
I completely forgot about custom formatting a cell. I used your
suggestions. What if I wanted a space and the year in the formatting
statement like this.

Sub test()
Worksheets("Report").Range("A1").Value = _
Format(Worksheets("WKO").Range("C3").Value, "mmmm yyyy")
End Sub

When I try this it changes the format to "mmmm-yy" when viewed on the
worksheet.

Thanks, Mark.

 

[Bernie Deitrick]

Mark,

The changes that you made causes your sub to return a valid date string to Excel: "August 2005" is a
valid date string that Excel enters as 38565, the serial number for August 1, 2005. "August" is not
a valid date string, so Excel leaves it alone.

You can either
1) format the cell A1 on Report as Text or
2) use a custom format for that cell of mmmm yyyy

Or 3) change the macro to return a string with a leading single quote:

Worksheets("Report").Range("A1").Value = _
"'" & Format(Worksheets("WKO").Range("C3").Value, "mmmm yyyy")

HTH,
Bernie
MS Excel MVP

 

[cbetmark]

Bernie,
Thanks the single quote worked great.

Mark