determining datatype
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!
|