Using the Find method of a Range object

[JAC]

I am trying unsuccessfully to use the Find method of a Range object in
VBA to locate dates in a column of data.

StatementDate is a property that maps to a Date type
DatesColumn and rngFind are Ranges

Set rngFind = DatesColumn.Find(What:=CStr(StatementDate),
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:=False)

This works, but if I remove the CStr() coercion, it refuses to find
the date. The documentation in Help for Excel 2003 suggests that you
can use any data type for the search, but it only seems to work for
strings.

Does anyone have any experience using Find successfully for Dates? I
have only ever used the Find method to locate strings of text
previously. If so, can you advise please.

Thank you

 

[Dave Peterson]

I usually try clng() instead of cstr().

That seems to work ok -- well, in my experience.

 

[JAC]

I usually try clng() instead of cstr().

That seems to work ok -- well, in my experience.

Thanks, Dave.

That would make sense, since the date component of a Date type
consists of a number whose integer part is a day number and the
fractional part is the time.

I shall try this, but I am still open to alternative suggestions,
since they can lead to elegant results sometimes.

JAC