Loop to build End-of-Month Dates.

Hi all, I turn to you for a better way than what I would do.
 I am
looking to build a loop of 24 increments that will take the
current
month and make a list of 24 monthly dates ranged around the
current
date.  These dates must be of the last calendar day of each
month in a
range from 12 months prior to the current month till 12
month past the
current month.  I want to avoid storing a date-list on a
worksheet and
build the list via VBA.

 

Hope that makes sense, Vincent...

 

PS, Thanks...



"This e-mail and any attachments to it (the
"Communication") is, unless otherwise stated,
confidential,  may contain copyright material and is for the
use only of the intended recipient. If you receive the
Communication in error, please notify the sender immediately
by return e-mail, delete the Communication and the return
e-mail, and do not read, copy, retransmit or otherwise deal
with it. Any views expressed in the Communication are those
of the individual sender only, unless expressly stated to be
those of Australia and New Zealand Banking Group Limited ABN
11 005 357 522, or any of its related entities including ANZ
National Bank Limited (together "ANZ"). ANZ does
not accept liability in connection with the integrity of or
errors in the Communication, computer virus, data
corruption, interference or delay arising from or in respect
of the Communication."

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

Just use a loop with the DATE() function.  For example:

=DATE(YEAR(NOW()),MONTH(NOW())+increment,0)

...where "increment" goes from -12 to +12 (or
whatever you need).
Using a value of zero for the day in that function gets you
the last
day of the prior month.

On 3/5/07, Bayliss, Vincent <baylissvanz.com> wrote:
> Hi all, I turn to you for a better way than what I
would do.  I am
> looking to build a loop of 24 increments that will take
the current
> month and make a list of 24 monthly dates ranged around
the current
> date.  These dates must be of the last calendar day of
each month in a
> range from 12 months prior to the current month till 12
month past the
> current month.  I want to avoid storing a date-list on
a worksheet and
> build the list via VBA.

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

This is basically Bob Phillip's idea, which only needed a
little tweaking for me to get it to work.

Dim i As Integer, aryDates(23) As Variant
For i = -11 To 12
  aryDates(i + 11) = DateSerial(Year([A2]), Month([A2]) + i,
0)
Next i
Range("B2:B25") = Application.Transpose(aryDates)


Regards
Robert McCurdy
----- Original Message ----- 
From: "Bayliss, Vincent" <baylissvANZ.COM>
To: <EXCEL-GPEACH.EASE.LSOFT.COM>
Sent: Tuesday, March 06, 2007 7:21 PM
Subject: Loop to build End-of-Month Dates.


Hi all, I turn to you for a better way than what I would do.
 I am
looking to build a loop of 24 increments that will take the
current
month and make a list of 24 monthly dates ranged around the
current
date.  These dates must be of the last calendar day of each
month in a
range from 12 months prior to the current month till 12
month past the
current month.  I want to avoid storing a date-list on a
worksheet and
build the list via VBA.

 

Hope that makes sense, Vincent...

 
PS, Thanks...

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G