increase date by month
 

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

increase date by month
 

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

increase date by month
 

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

increase date by month
 

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