I have a sheet in which A3 will always be the 1st of the month, formatted
as 'd'. I want the code (NOT a formula or function) to look at that date
and change it to the 1st of next month, so I can then autofill days down
the column also with code (I know the code for that). I thought this would
be so easy since I've worked with much more complicated date routines, but
just can't come up with the code.

Range("A3")= ????

Any help?

--
David

Range("A3").Value = DateAdd("m", 1, Range("A3").Value)

HTH

Geoff


"David Turner" wrote in message
...
I have a sheet in which A3 will always be the 1st of the month, formatted
as 'd'. I want the code (NOT a formula or function) to look at that date
and change it to the 1st of next month, so I can then autofill days down
the column also with code (I know the code for that). I thought this would
be so easy since I've worked with much more complicated date routines, but
just can't come up with the code.

Range("A3")= ????

Any help?

--
David

GB wrote

Range("A3").Value = DateAdd("m", 1, Range("A3").Value)

Excellent!!

Much more compact (I like compact) than what I finally stumbled onto:

dte = DateValue(Range("A3").Value)
Range("A3") = DateSerial(Year(dte), Month(dte) + 1, 1)

Thanks.

--
David

"David Turner" wrote in message
...
GB wrote

Range("A3").Value = DateAdd("m", 1, Range("A3").Value)

Excellent!!

Much more compact (I like compact) than what I finally stumbled onto:

dte = DateValue(Range("A3").Value)
Range("A3") = DateSerial(Year(dte), Month(dte) + 1, 1)

Thanks.

--
David

Thanks David.

I take it that you can also find it difficult to track down the function
that you want to use in VBA. I think that that is because VBA is rather
inconsistent with the underlying apps.

For example, the Date function in Excel worksheets does the same as
DateSerial in VBA, whereas the Date function in VBA returns the current
system date. The reason is that other Office applications like word use the
Date 'field' to mean current system date, whilst Excel uses Today or Now for
that.

Hardly surprising that VBA is so inconsistent and arcane in places when the
underlying applications were clearly written by different teams who did not
speak to each other. In fact, I seem to recall that the applications were
originally bought in by Microsoft from different companies and merely
branded together as Office. VBA is where the different apps meet up, and
that's where the inconsistencies stand out.

Geoff

GB wrote

I take it that you can also find it difficult to track down the function
that you want to use in VBA.

True. Curious, I plugged in "DateAdd("m", 1, Range" to a Google Groups
search and got several hits that would have provided the answer that was
better than my awkward adaptation of what seemed to be the "traditional"
responses to "first day of next month"-type queries.

I guess I have to hone my search skills <g.

--
David

VBA is a derivative of VB which has no relation to office at all. VB is a
derivative of basic which has been around a lot longer than office. VBA was
later chosen to be a common scripting capability in Office and other apps -
such as Autocad (which all have a legacy macro language (autocad uses a
form of lisp)). Any consistency is probably more of a coincidence.

--
Regards,
Tom Ogilvy


"GB" wrote in message
...

"David Turner" wrote in message
...
GB wrote

Range("A3").Value = DateAdd("m", 1, Range("A3").Value)

Excellent!!

Much more compact (I like compact) than what I finally stumbled onto:

dte = DateValue(Range("A3").Value)
Range("A3") = DateSerial(Year(dte), Month(dte) + 1, 1)

Thanks.

--
David

Thanks David.

I take it that you can also find it difficult to track down the function
that you want to use in VBA. I think that that is because VBA is rather
inconsistent with the underlying apps.

For example, the Date function in Excel worksheets does the same as
DateSerial in VBA, whereas the Date function in VBA returns the current
system date. The reason is that other Office applications like word use
the
Date 'field' to mean current system date, whilst Excel uses Today or Now
for
that.

Hardly surprising that VBA is so inconsistent and arcane in places when
the
underlying applications were clearly written by different teams who did
not
speak to each other. In fact, I seem to recall that the applications were
originally bought in by Microsoft from different companies and merely
branded together as Office. VBA is where the different apps meet up, and
that's where the inconsistencies stand out.

Geoff

DateAdd is good for the task you describe. What you call the "traditional"
is pretty resiliant - for example, it can give you the last day of the month
from any given date.


--
Regards,
Tom Ogilvy




"David Turner" wrote in message
...
GB wrote

I take it that you can also find it difficult to track down the function
that you want to use in VBA.

True. Curious, I plugged in "DateAdd("m", 1, Range" to a Google Groups
search and got several hits that would have provided the answer that was
better than my awkward adaptation of what seemed to be the "traditional"
responses to "first day of next month"-type queries.

I guess I have to hone my search skills <g.

--
David

"Tom Ogilvy" wrote in message
...
<snip
Any consistency is probably more of a coincidence.

--
Regards,
Tom Ogilvy


LOL - don't you still use Basica?


The COMPAQ Personal Computer BASIC
Version 3.11

(C) Copyright COMPAQ Computer Corp. 1982, 83, 84, 85
(C) Copyright Microsoft 1983,1984
61450 Bytes free
Ok
? date$
10-08-2003
Ok



1LIST 2RUN 3LOAD" 4SAVE" 5CONT 6,"LPT1 7TRON 8TROFF 9KEY
0SCREEN