vba code needed for date

  • Thread starter Thread starter David Turner
  • Start date Start date
D

David Turner

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?
 
GB wrote
Range("A3").Value = DateAdd("m", 1, Range("A3").Value)
Click to expand...

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 Turner said:
GB wrote


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.
Click to expand...

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.
Click to expand...

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>.
 
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.
 
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.
 
Any consistency is probably more of a coincidence.
Click to expand...

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
 
Tom Ogilvy wrote
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.
Click to expand...

Agreed. The frustration was that none of the VBA examples I saw involved a
cell address. I believe the code I stumbled my way into came after studying
one of your offerings that used VBA's immediate window to test results.
 
Back
Top