A cell in an Excel workbook contains a date. I want to write macro code to
generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not seem
to support the function. I can generate the date using the DATE function,
and I tried using Application.WorksheetFunction in my macro, but it did not
work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the next
month would be appreciated.

=EOMONTH(C4;0)+1
where C4 is your date ? format it of course as you wish

tomek

U¿ytkownik "Dianne Groom" napisa³ w wiadomo¶ci
...
A cell in an Excel workbook contains a date. I want to write macro code to
generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not
seem to support the function. I can generate the date using the DATE
function, and I tried using Application.WorksheetFunction in my macro, but
it did not work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the
next month would be appreciated.

ups.
Analysis ToolPack must be on

tomek

U¿ytkownik "tomek gomek" napisa³ w wiadomo¶ci
...
=EOMONTH(C4;0)+1
where C4 is your date ? format it of course as you wish

tomek

U¿ytkownik "Dianne Groom" napisa³ w wiadomo¶ci
...
A cell in an Excel workbook contains a date. I want to write macro code
to generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not
seem to support the function. I can generate the date using the DATE
function, and I tried using Application.WorksheetFunction in my macro,
but it did not work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the
next month would be appreciated.

Assuming you have a date in A1, in another cell enter:

=DATE(YEAR(A1),MONTH(A1)+1,1)

VBA is similar, but use DateSerial instead of Date.

As for EOMONTH, this is supplied by the Analysis Tool Pack add-in, which may
not be installed. Check ToolsAddins in Excel.

NickHK

"Dianne Groom" wrote in message
...
A cell in an Excel workbook contains a date. I want to write macro code
to
generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not
seem
to support the function. I can generate the date using the DATE function,
and I tried using Application.WorksheetFunction in my macro, but it did
not
work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the
next
month would be appreciated.

Try the below code

Dim mDate As Date
Dim mStr
mDate = DateAdd("m", 1, Range("L3").Value)
mStr = "01-" & Format(mDate, "mmm-yy")
Range("M3").Value = Format(mStr, "dd-mmm-yy")

Hope this helps!!
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Dianne Groom" wrote:

A cell in an Excel workbook contains a date. I want to write macro code to
generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not seem
to support the function. I can generate the date using the DATE function,
and I tried using Application.WorksheetFunction in my macro, but it did not
work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the next
month would be appreciated.

On Fri, 27 Jul 2007 14:24:53 +0800, "Dianne Groom"
wrote:

A cell in an Excel workbook contains a date. I want to write macro code to
generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not seem
to support the function. I can generate the date using the DATE function,
and I tried using Application.WorksheetFunction in my macro, but it did not
work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the next
month would be appreciated.


Sub FirstOfMonth()
Dim src As Range
Dim dest As Range

Set src = Range("A1")
Set dest = Range("A2")

If IsDate(src.Text) Then
dest.Value = src.Value - Day(src.Value) + 1
End If

End Sub

--ron

"Ron Rosenfeld" wrote...
"Dianne Groom" wrote...
A cell in an Excel workbook contains a date. I want to write macro code
to generate, in another cell, the date of the first day of the next month.
....
Sub FirstOfMonth()
....
If IsDate(src.Text) Then
dest.Value = src.Value - Day(src.Value) + 1
End If

End Sub

That sets dest to the first day of the same month as src. For the next
month, it requires something like

dest.Value = src.Value - Day(src.Value) + 33 _
- Day(src.Value - Day(src.Value) + 32)

On Fri, 27 Jul 2007 21:36:55 -0700, "Harlan Grove" wrote:

"Ron Rosenfeld" wrote...
"Dianne Groom" wrote...
A cell in an Excel workbook contains a date. I want to write macro code
to generate, in another cell, the date of the first day of the next month.
...
Sub FirstOfMonth()
...
If IsDate(src.Text) Then
dest.Value = src.Value - Day(src.Value) + 1
End If

End Sub

That sets dest to the first day of the same month as src. For the next
month, it requires something like

dest.Value = src.Value - Day(src.Value) + 33 _
- Day(src.Value - Day(src.Value) + 32)


<Sigh. Thanks. I misread the question.
--ron