I'M STUCK!!! Trying to add dates in VBA.

[chouston]

Can anyone tell me why this won't work???

--
Dim endcycle As Date
Dim newstartcycle As Date

newstartcycle = CDate(InputBox("Enter beginning date of billing cycle."))

endcycle = DateAdd(m, 1, newstartcycle)
--

thanks in advance to whomever solves this!

 

[chouston]

additionally, how can I use VBA to return a date that is the 1st of the next month?

e.g. 3/15 --> 4/1
8/15 --> 9/1

etc.

Thanks again if you can answer this

 

[Jay Petrulis]

Add quotes around the 'm' in the DateAdd

Change:
endcycle = DateAdd(m, 1, newstartcycle)
to
endcycle = DateAdd("m", 1, newstartcycle)

 

[Jay Petrulis]

On 2002-05-06 16:17, chouston wrote:
additionally, how can I use VBA to return a date that is the 1st of the next month?

e.g. 3/15 --> 4/1
8/15 --> 9/1

etc.

Thanks again if you can answer this

One way:

DateSerial(Year(endcycle), Month(endcycle) + 1, 1)

Another is to use the EOMONTH function, but you have to set references to the Analysis ToolPak to call the function.

 

[chouston]

THANKS!

I ended up cheating on the second part though:
--
newstartcycle = InputBox("Enter beginning date of billing cycle.")

endcycle = DateAdd("m", 1, newstartcycle)
nextdate = DateAdd("m", 1, endcycle)
duedate = DateAdd("d", -14, nextdate)
--
it's not fancy, but it works, Thanks again.

 

[NateO]

You may want to validate the date, i.e., application.inputbox....type:=1, at the very least, you'll get a number.