Very tricky, thanks, Bernie!
--
Regards!
Stefi



€žBernie Deitrick€� ezt Ã*rta:

Stefi,

The Find method uses the worksheet find dialog, which is set up to find the
displayed value, not the underlying value.

And, in case the formatting is ever changed, it is better to use code that
reads the formatting prior to the Find:

Range("first range").Find(Format(DateToFind, Range("first
range").Cells(1,1).NumberFormat)).Row 0

HTH,
Bernie
MS Excel MVP


"Stefi" wrote in message
...
Hi All,

I have two ranges of dates, both one's width is one column in which dates
are created by =DATE() functions. In the first range cells are formatted
like
"yyyy.mm.dd", in the 2nd one like "mmmm d.".
In a macro I have a date variable DateToFind created by a DateSerial()
function.

I used
Range("first range").Find(What:=DateToFind ).Row 0

to check if DateToFind is included in first range and it worked.
For the second range it gave a Type mismatch error, and i had to modify
the
Find line like this to make it work:
Range("first range").Find(What:=Format(DateToFind , "mmmm d.")).Row 0

I can't find out what is the cause of this error, because both the range I
search in and the value I want to find are real Excel date values
(numbers),
only the display format is different. In other words: why does Excel find
the
numeric value of the date in the first range and why the display format
string in the second string?

--
Regards!
Stefi



.