I'm a relative newby with VBA. I need to return the last day of the month
for a series of dates. I have found this formula many places on the web, but
get a compile error with it.

dtEOM =DATE(YEAR(A1),MONTH(A1)+1,0)

where dtEOM is my vairable declared as a Date. The error is a
'Compile Error: Expected: )'
and YEAR is highlighted.

My intent is to fill dtEOM with the month, create some record counts, and go
to the next month, reset the variable, and do it again. Your help is
appreciated.

Not YEAR(A1)
but
YEAR(Range("A1").Value)
--
Gary's Student
gsnu200703


"Wiley" wrote:

I'm a relative newby with VBA. I need to return the last day of the month
for a series of dates. I have found this formula many places on the web, but
get a compile error with it.

dtEOM =DATE(YEAR(A1),MONTH(A1)+1,0)

where dtEOM is my vairable declared as a Date. The error is a
'Compile Error: Expected: )'
and YEAR is highlighted.

My intent is to fill dtEOM with the month, create some record counts, and go
to the next month, reset the variable, and do it again. Your help is
appreciated.

Thanks, but I still get the error. Here is the edited line I am using now:

dtEOM = DATE(YEAR(Range("D2").Value),MONTH(Range("D2").Val ue)+1,0)

Ultimately, I want to return the date on a vairable which is the first day
fo the month, so I beleive I want ti to look like this:

dtEOM = DATE(YEAR(dtBOM),MONTH(dtBOM)+1,0)

But that gives me the same error. As a test I have set dtBOM prioor to this
line as:
dtBOM = "06/01/2006"

NOTE: I would use the EOM function in Excel, but as a guest on this PC, I
cannot install the Analysis Toolpak.

"Gary''s Student" wrote:

Not YEAR(A1)
but
YEAR(Range("A1").Value)
--
Gary's Student
gsnu200703


"Wiley" wrote:

I'm a relative newby with VBA. I need to return the last day of the month
for a series of dates. I have found this formula many places on the web, but
get a compile error with it.

dtEOM =DATE(YEAR(A1),MONTH(A1)+1,0)

where dtEOM is my vairable declared as a Date. The error is a
'Compile Error: Expected: )'
and YEAR is highlighted.

My intent is to fill dtEOM with the month, create some record counts, and go
to the next month, reset the variable, and do it again. Your help is
appreciated.

try:

dtEOM = dateserial(year(.....

=date() is a worksheet function.


Wiley wrote:

Thanks, but I still get the error. Here is the edited line I am using now:

dtEOM = DATE(YEAR(Range("D2").Value),MONTH(Range("D2").Val ue)+1,0)

Ultimately, I want to return the date on a vairable which is the first day
fo the month, so I beleive I want ti to look like this:

dtEOM = DATE(YEAR(dtBOM),MONTH(dtBOM)+1,0)

But that gives me the same error. As a test I have set dtBOM prioor to this
line as:
dtBOM = "06/01/2006"

NOTE: I would use the EOM function in Excel, but as a guest on this PC, I
cannot install the Analysis Toolpak.

"Gary''s Student" wrote:

Not YEAR(A1)
but
YEAR(Range("A1").Value)
--
Gary's Student
gsnu200703


"Wiley" wrote:

I'm a relative newby with VBA. I need to return the last day of the month
for a series of dates. I have found this formula many places on the web, but
get a compile error with it.

dtEOM =DATE(YEAR(A1),MONTH(A1)+1,0)

where dtEOM is my vairable declared as a Date. The error is a
'Compile Error: Expected: )'
and YEAR is highlighted.

My intent is to fill dtEOM with the month, create some record counts, and go
to the next month, reset the variable, and do it again. Your help is
appreciated.


--

Dave Peterson

Thanks Dave. You headed me in the right direction. Now I know the
difference between a worksheet function and a VBA function. You get the
kudos for that. I'll work with dateserial to see what I can come up with.

"Dave Peterson" wrote:

try:

dtEOM = dateserial(year(.....

=date() is a worksheet function.


Wiley wrote:

Thanks, but I still get the error. Here is the edited line I am using now:

dtEOM = DATE(YEAR(Range("D2").Value),MONTH(Range("D2").Val ue)+1,0)

Ultimately, I want to return the date on a vairable which is the first day
fo the month, so I beleive I want ti to look like this:

dtEOM = DATE(YEAR(dtBOM),MONTH(dtBOM)+1,0)

But that gives me the same error. As a test I have set dtBOM prioor to this
line as:
dtBOM = "06/01/2006"

NOTE: I would use the EOM function in Excel, but as a guest on this PC, I
cannot install the Analysis Toolpak.

"Gary''s Student" wrote:

Not YEAR(A1)
but
YEAR(Range("A1").Value)
--
Gary's Student
gsnu200703


"Wiley" wrote:

I'm a relative newby with VBA. I need to return the last day of the month
for a series of dates. I have found this formula many places on the web, but
get a compile error with it.

dtEOM =DATE(YEAR(A1),MONTH(A1)+1,0)

where dtEOM is my vairable declared as a Date. The error is a
'Compile Error: Expected: )'
and YEAR is highlighted.

My intent is to fill dtEOM with the month, create some record counts, and go
to the next month, reset the variable, and do it again. Your help is
appreciated.


--

Dave Peterson