Determine begin month date from month end date.

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

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

"mikeburg" wrote:

>
> 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
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=501127
>
>

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" wrote:

>
> 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
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=501127
>
>

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

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

Thanks very, very much. mikeburg