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)

--
cbetmar
-----------------------------------------------------------------------
cbetmark's Profile: http://www.excelforum.com/member.php...fo&userid=2619
View this thread: http://www.excelforum.com/showthread.php?threadid=39500

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" wrote in message
...

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,
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"
wrote in message
...

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

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" wrote in message
...

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"
wrote in message
...

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

Bernie,
Thanks the single quote worked great.

Mark

--
cbetmar
-----------------------------------------------------------------------
cbetmark's Profile: http://www.excelforum.com/member.php...fo&userid=2619
View this thread: http://www.excelforum.com/showthread.php?threadid=39500