I want a macro to increase a date given from a text box
by month up to 20 times and put the last day of the date at cells.

For example txt1.value = 30/06/03
then a loop must be fired

for da=1 to 20

cells(da,1).value= ?????? (last day of then next month 31/07/2003)
etc

next da


and change also the year if the code pass the last day 31/12

Thanks in advance

Private Sub TextBox1_AfterUpdate()
Dim dtX As Date
Dim iX As Long

dtX = TextBox1.Text
dtX = DateAdd("m", 20, dtX)
iX = DateSerial(Year(dtX), Month(dtX) + 1, 1) - 1
dtX = iX
MsgBox dtX

End Sub

HTH,
Merjet

Gus,

Here's some code that does the basics

Dim da As Long
Dim myDate As Date
Dim newdate As Date

myDate = CDate(TextBox1.Value)
For da = 1 To 20
newdate = DateSerial(Year(myDate), Month(myDate) + 1, Day(myDate))
If Month(newdate) Month(myDate) + 1 Then
MsgBox "help!!"
Exit For
Else
myDate = newdate
End If
Next da

It also highlights a basic problem. If the date of 31/01/2003 is entered,
when you add 1 month to that it becomes the 31/02/03 or 03/03/03 (that's how
Excel handles dates!). So is this correct, or do you want to revert to the
01/03/03, i.e. the first of the following month, or 2w8/02/03, i.e. the last
of that next month? And then what do you do the following month when there
ared 31 again.

Also, why add them in a loop, why not just add 20 months in one swoopp
myDate = CDate(TextBox1.Value)
newdate = DateSerial(Year(myDate), Month(myDate) + 20, Day(myDate))
You still have a date problem, but only one, not 20.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"GUS" wrote in message
...
I want a macro to increase a date given from a text box
by month up to 20 times and put the last day of the date at cells.

For example txt1.value = 30/06/03
then a loop must be fired

for da=1 to 20

cells(da,1).value= ?????? (last day of then next month 31/07/2003)
etc

next da


and change also the year if the code pass the last day 31/12

Thanks in advance

A possible modification that should give you the date of the last day of
each month (as that is what I understood you to require).

Private Sub CommandButton1_Click()
Dim newDate As Date
Dim myDate As Date
Dim da As Long

myDate = CDate(TextBox1.Value)
For da = 1 To 20
newDate = DateSerial(Year(myDate), Month(myDate) + 2, 0)
Cells(da, 1).Value = newDate
myDate = newDate
Next da

End Sub


Worked fine for me.

--
Regards,
Tom Ogilvy




Bob Phillips wrote in message
...
Gus,

Here's some code that does the basics

Dim da As Long
Dim myDate As Date
Dim newdate As Date

myDate = CDate(TextBox1.Value)
For da = 1 To 20
newdate = DateSerial(Year(myDate), Month(myDate) + 1, Day(myDate))
If Month(newdate) Month(myDate) + 1 Then
MsgBox "help!!"
Exit For
Else
myDate = newdate
End If
Next da

It also highlights a basic problem. If the date of 31/01/2003 is entered,
when you add 1 month to that it becomes the 31/02/03 or 03/03/03 (that's
how
Excel handles dates!). So is this correct, or do you want to revert to the
01/03/03, i.e. the first of the following month, or 2w8/02/03, i.e. the
last
of that next month? And then what do you do the following month when there
ared 31 again.

Also, why add them in a loop, why not just add 20 months in one swoopp
myDate = CDate(TextBox1.Value)
newdate = DateSerial(Year(myDate), Month(myDate) + 20, Day(myDate))
You still have a date problem, but only one, not 20.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"GUS" wrote in message
...
I want a macro to increase a date given from a text box
by month up to 20 times and put the last day of the date at cells.

For example txt1.value = 30/06/03
then a loop must be fired

for da=1 to 20

cells(da,1).value= ?????? (last day of then next month
31/07/2003)
etc

next da


and change also the year if the code pass the last day 31/12

Thanks in advance