Find date

[Stefi]

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?

 

[Bernie Deitrick]

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]

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



„Bernie Deitrick†ezt írta:

 

[Rick Rothstein]

I think she could have done it this way as well...

Range("first range").Find(What:=DateToFind, LookIn:=xlFormulas).Row > 0

--
Rick (MVP - Excel)

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]

Hi Rick,

I tried your solution but it didn't work. To say the truth, I had to add
Lookin:=xlValues to Bernie's solution to make it work safely.

--
Regards!
Stefi



„Rick Rothstein†ezt írta:

I think she could have done it this way as well...

Range("first range").Find(What:=DateToFind, LookIn:=xlFormulas).Row > 0