I am trying to do a basic macro where I do some math on a series of dates to
determine the number of days between them. In some fields in the worksheet
there are entries and in some there are not. Because of this I only want to
do a calculation if both fields contain data. I find that in excel the
IsNumber() function will return if a date is in the field alright, but when I
try to use this line in an If statement in VBA:

Application.WorksheetFunction.IsNumber(Cells(row, col1).Value)

FALSE is always retuned. The cell I checked has 1/5/2005 in it. I used the
locals window to view the value of a temp variable I assigned and it shows
"#1/5/2005#".

Do I need to recast Cells(row,col1).Value as an integer and if so how do I
do it? Also is there a better way to do this?

In VBA there is a function IsNumeric... That might be the ticket here.
--
HTH...

Jim Thomlinson


"brent" wrote:

I am trying to do a basic macro where I do some math on a series of dates to
determine the number of days between them. In some fields in the worksheet
there are entries and in some there are not. Because of this I only want to
do a calculation if both fields contain data. I find that in excel the
IsNumber() function will return if a date is in the field alright, but when I
try to use this line in an If statement in VBA:

Application.WorksheetFunction.IsNumber(Cells(row, col1).Value)

FALSE is always retuned. The cell I checked has 1/5/2005 in it. I used the
locals window to view the value of a temp variable I assigned and it shows
"#1/5/2005#".

Do I need to recast Cells(row,col1).Value as an integer and if so how do I
do it? Also is there a better way to do this?

Jim that does work, additionally I also found that CLng() does a great job on
converting my date to a long so that I can check to see if it is blank (will
= 0).

"Jim Thomlinson" wrote:

In VBA there is a function IsNumeric... That might be the ticket here.
--
HTH...

Jim Thomlinson


"brent" wrote:

I am trying to do a basic macro where I do some math on a series of dates to
determine the number of days between them. In some fields in the worksheet
there are entries and in some there are not. Because of this I only want to
do a calculation if both fields contain data. I find that in excel the
IsNumber() function will return if a date is in the field alright, but when I
try to use this line in an If statement in VBA:

Application.WorksheetFunction.IsNumber(Cells(row, col1).Value)

FALSE is always retuned. The cell I checked has 1/5/2005 in it. I used the
locals window to view the value of a temp variable I assigned and it shows
"#1/5/2005#".

Do I need to recast Cells(row,col1).Value as an integer and if so how do I
do it? Also is there a better way to do this?