Can someone pick holes in this code as it fails.

ActiveCell.Formula = "=if datepart(""yyyy"",(e2)) =
datepart(""yyyy"",now()+1),date(1,month(e2),day(e2 )),date(0,month(e2),day(e2))"

how does it fail?

Tim

--
Tim Williams
Palo Alto, CA


"Ron Dean" wrote in message
. nl...
Can someone pick holes in this code as it fails.

ActiveCell.Formula = "=if datepart(""yyyy"",(e2)) =

datepart(""yyyy"",now()+1),date(1,month(e2),day(e2 )),date(0,month(e2),day(e2
))"

On Thu, 5 Jan 2006 20:59:05 +0100, "Ron Dean" wrote:

Can someone pick holes in this code as it fails.

ActiveCell.Formula = "=if datepart(""yyyy"",(e2)) =
datepart(""yyyy"",now()+1),date(1,month(e2),day(e 2)),date(0,month(e2),day(e2))"


DATEPART is not an Excel worksheet function

Maybe you could replace it with the YEAR worksheet function.

Also, by entering either a 1 or a 0 as the first argument of the DATE function,
this will give you either 1900 or 1901. Depending on what you are doing with
this data, you may run into a problem as Excel believes that 29 Feb 1900 is a
legitimate date, and that there are 366 days in 1900.


--ron

I'm using it in Excel VBA to sort previous and next year's dates.

Even using this simple code I get "application-defined or object-defined
error"

ActiveCell.Formula = "=if datepart((""yyyy"",[e2]) = ""00"",""N"",""y"""")"

I think I have braces in the wrong place

thanks

++++++++++++++++++++++++++

"Ron Rosenfeld" wrote in message
...
On Thu, 5 Jan 2006 20:59:05 +0100, "Ron Dean" wrote:

Can someone pick holes in this code as it fails.

ActiveCell.Formula = "=if datepart(""yyyy"",(e2)) =
datepart(""yyyy"",now()+1),date(1,month(e2),day( e2)),date(0,month(e2),day(e2))"


DATEPART is not an Excel worksheet function

Maybe you could replace it with the YEAR worksheet function.

Also, by entering either a 1 or a 0 as the first argument of the DATE
function,
this will give you either 1900 or 1901. Depending on what you are doing
with
this data, you may run into a problem as Excel believes that 29 Feb 1900
is a
legitimate date, and that there are 366 days in 1900.


--ron

Sorry that should have read "I am trying to sort old and next year's dates
on month and day

==============
"Ron Dean" wrote in message
. nl...
I'm using it in Excel VBA to sort previous and next year's dates.

Even using this simple code I get "application-defined or object-defined
error"

ActiveCell.Formula = "=if datepart((""yyyy"",[e2]) =
""00"",""N"",""y"""")"

I think I have braces in the wrong place

thanks

++++++++++++++++++++++++++

"Ron Rosenfeld" wrote in message
...
On Thu, 5 Jan 2006 20:59:05 +0100, "Ron Dean" wrote:

Can someone pick holes in this code as it fails.

ActiveCell.Formula = "=if datepart(""yyyy"",(e2)) =
datepart(""yyyy"",now()+1),date(1,month(e2),day (e2)),date(0,month(e2),day(e2))"


DATEPART is not an Excel worksheet function

Maybe you could replace it with the YEAR worksheet function.

Also, by entering either a 1 or a 0 as the first argument of the DATE
function,
this will give you either 1900 or 1901. Depending on what you are doing
with
this data, you may run into a problem as Excel believes that 29 Feb 1900
is a
legitimate date, and that there are 366 days in 1900.


--ron

On Fri, 6 Jan 2006 10:48:50 +0100, "Ron Dean" wrote:

I'm using it in Excel VBA to sort previous and next year's dates.

Even using this simple code I get "application-defined or object-defined
error"

ActiveCell.Formula = "=if datepart((""yyyy"",[e2]) = ""00"",""N"",""y"""")"

I think I have braces in the wrong place

thanks

Maybe I am not understanding what you are trying to do.

What you ARE doing is setting the Formula property of ActiveCell. In other
words, your statement will write the contents of that string into whatever the
ActiveCell is in your Active Worksheet.

That will not work for the reasons I previously stated -- the formula is not
valid within Excel.

If you really want to do that, the proper format of your statement is:

ActiveCell.Formula = "=If(DatePart(""yyyy"", e2) = ""00"", ""N"", ""y"")"

What that will do is write your formula into ActiveCell. ActiveCell will then
show a #NAME? error since DatePart is not a valid Excel worksheet function.


--ron

Point taken, I am now using YEAR and it works fine.
However if you go to HELP in Excel VBA there is a description of DatePart
Function. This is what I based my formula on.

Many thanks Ron

+++++++++++++++++
"Ron Rosenfeld" wrote in message
...
On Fri, 6 Jan 2006 10:48:50 +0100, "Ron Dean"
wrote:

I'm using it in Excel VBA to sort previous and next year's dates.

Even using this simple code I get "application-defined or object-defined
error"

ActiveCell.Formula = "=if datepart((""yyyy"",[e2]) =
""00"",""N"",""y"""")"

I think I have braces in the wrong place

thanks

Maybe I am not understanding what you are trying to do.

What you ARE doing is setting the Formula property of ActiveCell. In
other
words, your statement will write the contents of that string into whatever
the
ActiveCell is in your Active Worksheet.

That will not work for the reasons I previously stated -- the formula is
not
valid within Excel.

If you really want to do that, the proper format of your statement is:

ActiveCell.Formula = "=If(DatePart(""yyyy"", e2) = ""00"", ""N"", ""y"")"

What that will do is write your formula into ActiveCell. ActiveCell will
then
show a #NAME? error since DatePart is not a valid Excel worksheet
function.


--ron

On Fri, 6 Jan 2006 14:26:39 +0100, "Ron Dean" wrote:

Point taken, I am now using YEAR and it works fine.
However if you go to HELP in Excel VBA there is a description of DatePart
Function. This is what I based my formula on.

DatePart is a valid **VBA** function. It is NOT an Excel *Worksheet* function.

I'm glad you've got it working.


--ron