Rowan Drummond wrote:
Hi Dave

As you have declared dteColCode as a date it's default value is a date
(12:00:00 AM for some reason). When you try to set it to the value of a
cell that is not a date that line of code is skipped because of the On
Error Resume Next statement. So the value of dteColCode is still the
default date.

Another option looks like this:

Dim dteColCode as Variant
On Error Resume Next
dteColCode = DateValue(Range("A1").value)
On Error Goto 0
if dteColCode = empty then
msgbox "not a date"
else
msgbox "valid date"
end if

Hope this helps
Rowan

davegb wrote:
My workaround didn't work either. I tested the result of the MONTH
function on the same cell/variable, and wrote code to bypass the
counting steps if it returned an error. It still counted it, even
though when I put the MONTH function in the spreadsheet, it returned a
#VALUE error. Still don't understand what's happening.


Thanks for your reply. I modified the code as per yours above, but it
still doesn't solve the problem entirely, which is that sometimes
there's a date in the cell, and sometimes it has "na" in it. When I
modified it based on your suggestion, it still hangs when I do the
MONTH function on it if the cell doesn't contain a date.
I gather from your reply, and others, that XL cannot determine if a
cell contains a date or not. So maybe what I have to do is to count
characters in the cell, and see if there are dashes in certain places.
Sounds rather convoluted. Do you know of any existing code I could
start with and modify to accomplish this purpose?
Thanks in advance.