Macro: MonthName Function, runtime error 424

[cbetmark]

I have a workbook with 2 worksheets named WKO and Report. I am trying to write a macro to look at a cell containing a date in worksheet WKO and put the month's name in a cell on worksheet Report. The date 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 j =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" <cbetmark.1tlxqq_1123776389.6284@excelforum-nospam.com> wrote in message
news:cbetmark.1tlxqq_1123776389.6284@excelforum-nospam.com...
>
> I have a workbook with 2 worksheets named WKO and Report. I am trying
> to write a macro to look at a cell containing a date in worksheet WKO
> and put the month's name in a cell on worksheet Report. The date
> 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 j
> =MonthName(Month(WKO!C3))
>
>
> --
> cbetmark
> ------------------------------------------------------------------------
> cbetmark's Profile: http://www.excelforum.com/member.php...o&userid=26193
> View this thread: http://www.excelforum.com/showthread...hreadid=395003
>

[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.

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" <cbetmark.1tlxqq_1123776389.6284@excelforum-nospam.com> wrote in message
news:cbetmark.1tlxqq_1123776389.6284@excelforum-nospam.com...
>
> I have a workbook with 2 worksheets named WKO and Report. I am trying
> to write a macro to look at a cell containing a date in worksheet WKO
> and put the month's name in a cell on worksheet Report. The date
> 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 j
> =MonthName(Month(WKO!C3))
>
>
> --
> cbetmark
> ------------------------------------------------------------------------
> cbetmark's Profile: http://www.excelforum.com/member.php...o&userid=26193
> View this thread: http://www.excelforum.com/showthread...hreadid=395003
>

[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" <cbetmark.1tm62f_1123787184.3138@excelforum-nospam.com> wrote in message
news:cbetmark.1tm62f_1123787184.3138@excelforum-nospam.com...
>
> 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 Wrote:
>> 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" <cbetmark.1tlxqq_1123776389.6284@excelforum-nospam.com>
>> wrote in message
>> news:cbetmark.1tlxqq_1123776389.6284@excelforum-nospam.com...
>> >
>> > I have a workbook with 2 worksheets named WKO and Report. I am
>> trying
>> > to write a macro to look at a cell containing a date in worksheet
>> WKO
>> > and put the month's name in a cell on worksheet Report. The date
>> > 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 j
>> > =MonthName(Month(WKO!C3))
>> >
>> >
>> > --
>> > cbetmark
>> >
>> ------------------------------------------------------------------------
>> > cbetmark's Profile:
>> http://www.excelforum.com/member.php...o&userid=26193
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=395003
>> >
>
>
> --
> cbetmark
> ------------------------------------------------------------------------
> cbetmark's Profile: http://www.excelforum.com/member.php...o&userid=26193
> View this thread: http://www.excelforum.com/showthread...hreadid=395003
>

[cbetmark]

Bernie,
Thanks the single quote worked great.

Mark.