Using Find Method of Rangeobject

I am having trouble with Find method recognizing a date formatted field below the varExistingRecord = "01/01/2002" - It doesn't work, but when I try and find a field that is entirely string data it works fine...Any tips or tricks? Excel '97 thanks.



Private Sub UpdateExistingRecord()
Dim varExistingRow As Variant

With Worksheets(1).Range("a1:a6500")
Set varExistingRow = .Find(varExistingRecord, LookIn:=xlFormulas)
If Not varExistingRow Is Nothing Then
MsgBox ("i found it" & varExistingRecord)
Else
MsgBox (" i didn't find!" & varExistingRecord)

End If
End With
End Sub

That's because your search value is of a string data type and not a date data type. Try this:

VB Code:

Private Sub CommandButton1_Click()
    Dim rngDateColCells As Range
    Dim rngFirstMatch As Range
    
    Set rngDateColCells = Range("A1:A3")
    Set rngFirstMatch = rngDateColCells.Find(CDate("02/01/2001"))
    
    If Not (rngFirstMatch Is Nothing) Then
        MsgBox rngFirstMatch.Address
    End If
End Sub