Bob Phillips wrote:
11-23-05 is a valid date to VBA (it uses US date format), but it rejects
invalid dates and na for me.

Perhaps show the full code and give some sample data to better see.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Bob,
Thanks for your reply. I don't think it's the data. When I run the
MONTH function on it, from the macro or in the spreadsheet, it works
fine. And I tried re-entering the date to see if it made any
difference, which it didn't.
The code is:
Sub CountMonth()

Dim lngRsnCode As Long
Dim wksSrc As Worksheet
Dim wksMon As Worksheet
Dim wksTot As Worksheet
Dim rngCode As Range
Dim lEndRow As Long
Dim strMonWksht As String
Dim dteColCode As Date
Dim lngCntctMo As Long
Dim lngMoRow As Long
Dim strColCode As String
Dim rngCell As Range

Const PWORD As String = "2005totals"
lEndRow = 1000

Set wksSrc = ActiveSheet
Set wksTot = ActiveWorkbook.Sheets("TOTALS")
Set rngCode = wksSrc.Range("D8:D" & lEndRow)
wksTot.Unprotect Password:=PWORD

strMonWksht = wksSrc.Name & " - Monthly"
Set wksMon = Sheets(strMonWksht)
wksMon.Range("B4:K15").ClearContents

For Each rngCell In rngCode
rngCell.Select

If rngCell < "na" Then
If rngCell < "?" Then
If Len(rngCell) < 3 Then
If rngCell < 0 Then
If rngCell < 11 Then
If rngCell < 15 Then

On Error Resume Next
dteColCode = rngCell.Offset(0, 5).Value

rngCell.Offset(0, 6).Select
If IsDate(dteColCode) Then
lngCntctMo = Month(dteColCode)
lngMoRow = lngCntctMo + 3
lngRsnCode = rngCell
wksTot.Range("AC1") = lngRsnCode
strColCode = wksTot.Range("AC2")
wksMon.Cells(lngMoRow, strColCode) = _
wksMon.Cells(lngMoRow, strColCode) + 1
End If
End If
End If
End If
End If
End If
End If
Next rngCell

wksTot.Protect Password:=PWORD
wksTot.Select

End Sub

I have figured a workaround for the problem. Since the month function
works fine, I can test it. If it returns an error, then I can skip the
operation and go to the next cell. But I'd still like to know why the
ISDATE function doesn't work here. Any suggestions?
Thanks.



"davegb" wrote in message
oups.com...
I have a macro that looks for a date in a cell, then based on whether
or not it finds one, proceeds. But I can't find a way to test for a
date reliably. I did some research here, and I've tried "If
Isdate(variablename) then" and "If datatype (variablename)="date"
then", but neither works. Some of the tested cells contain "na", some
dates in the format 11-23-05, but the macro proceeds as if they were
all dates. Any ideas what could be amiss?
Thanks for the help, and Happy Thanksgiving!