Determine begin month date from month end date.

[mikeburg]

What would be the VBA code to determine the begin month date from the
month end date in cell F5 AND put in a variable named BeginMonthDate.

For example:

F5 = 09/30/2005
need BeginMonthDate to equal 09/01/2005

or

F5 = 12/31/2005
need BeginMonthDate to equal 12/01/2005

Thanks a million. mikeburg

 

[Giles]

Mike

You can do this in a couple of ways - one that doesn't need to use VBA is:

Go into Insert, Name, Define

Create the "variable" name BeginMonthDate in the "Names In Workbook" box and
enter the following in the "Refers To" box:

=EOMONTH(Sheet1!$F$5,-1)+1 <------ Requires Analysis toolpak to be
loaded
(change to refer to your sheet)

This name can then be used as a variable in any workbook in the normal way
or can be used in VBA by referring to it in square brackets -

e.g. MyStartMonth = [BeginMonthDate]

HTH

Giles

 

[GB]

if you set your variable = dateserial(Year(data in Cell), Month(data in
Cell), 1) then your date will be the first of the month so:

BeginMonthDate = DateSerial(Year(Sheet1.Cells("F5")),
Month(Sheet1.Cells("F5")), 1)

will set BeginMonthDate = 09/01/2005 if F5 = 09/30/2005
Then you can do whatever you want with BeginMonthDate.

 

[mikeburg]

I got an error using the above line. However, after I modified it t
the line below it worked great:

'MonthBeginDate = DateSerial(Year(Range("F5")), Month(Range("F5")), 1)

Thanks very, very much. mikebur