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!

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)


"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!

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!

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.

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.

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.

Hi Dave

The example I gave you should work to determine if there is a date or
not. If there is not a date then you need to do something other than the
Month command. In my example cell E11 is formatted as a date and
contains a vlookup formula. If I modify my data so that the formula
returns a date the code below returns the correct month. If I then
change the data again so that the formula returns a #N/A error the code
produces the message box stating that it is not a valid date.

So this example definately does determine whether it is a date or not,
it then is up to me (or you) as to what you do with the variable when it
is not a date (and hence is empty).

Code:

Dim dteColCode As Variant
On Error Resume Next
dteColCode = DateValue(Range("E10").Value)
On Error GoTo 0
If dteColCode = Empty Then
MsgBox "not a date"
Else
MsgBox Month(dteColCode)
End If

Regards
Rowan

davegb wrote:
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.

Rowan Drummond wrote:
Hi Dave

The example I gave you should work to determine if there is a date or
not. If there is not a date then you need to do something other than the
Month command. In my example cell E11 is formatted as a date and
contains a vlookup formula. If I modify my data so that the formula
returns a date the code below returns the correct month. If I then
change the data again so that the formula returns a #N/A error the code
produces the message box stating that it is not a valid date.

So this example definately does determine whether it is a date or not,
it then is up to me (or you) as to what you do with the variable when it
is not a date (and hence is empty).

Code:

Dim dteColCode As Variant
On Error Resume Next
dteColCode = DateValue(Range("E10").Value)
On Error GoTo 0
If dteColCode = Empty Then
MsgBox "not a date"
Else
MsgBox Month(dteColCode)
End If

Regards
Rowan

davegb wrote:
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.


Thanks, Rowan!

You're welcome.



Thanks, Rowan!