CODEWITHSUNDEEP
vbaexcel
Mike
Mike

Reputation: 1938

Why is my VBA .Find not finding anything?

I have what I thought was a pretty simple .Find, but it isn't working.

lngLastRow = wsFound.Range("D" & Rows.count).End(xlUp).Row
Set SearchRange = wsFound.Range("D1:D" & lngLastRow)
For Each a In wsFound.Range(wsFound.Range("D2"), wsFound.Range("D" & Rows.count).End(xlUp))
    With SearchRange
        Set c = .Find("01/03/1950", LookIn:=xlValues) 'a.Value, LookIn:=xlValues)
        If Not c Is Nothing Then
            Firstfind = a.Address
            Do
                wsFound.Range("A" & a.Row & ":U" & a.Row).Copy
                LastRow = ActiveWorkbook.Sheets("Duplicates").Range("A" & Rows.count).End(xlUp).Row + 1
                ActiveWorkbook.Sheets("Duplicates").Range("A" & LastRow).PasteSpecial
                Set c = .FindNext(c)
                If c Is Nothing Then
                    GoTo DoneFinding
                End If
            Loop While a.Address <> Firstfind
        End If

DoneFinding: End With Next a

I'm pretty sure that I've got this correct I used info from MSDN to get it this far.

But it isn't finding anything!

My data looks like this:

+--------------+---------+-----------+---------------+--------------+
|     A        |    B    |     C     |       D       |       E      |
+--------------+---------+-----------+---------------+--------------+
| Staff Number | Surname | Forenames | Date of Birth |  Address 1   |
+--------------+---------+-----------+---------------+--------------+
|   1000064036 | Farrell | Margaret  | 01/03/1950    | 11 The Close |
|   1000064036 | Farrell | Margaret  | 01/03/1950    | 11 The Close |
|   1000064036 | Farrell | Margaret  | 01/03/1950    | 11 The Close |
+--------------+---------+-----------+---------------+--------------+

So I know that there are 3 duplicates in the DOB D column that should be found.

Upvotes: 1

Views: 1022

Answers (1)

Mike
Mike

Reputation: 1938

I finally figured it out through some more searching as per a comment from David Zemens on making my search more specific.

Here is what worked:

For Each a In wsFound.Range(wsFound.Range("D2"), wsFound.Range("D" & Rows.count).End(xlUp))
    With SearchRange
        Set c = SearchRange.Find(a.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        If Not c Is Nothing Then
            Firstfind = a.Address
            Do
                wsFound.Range("A" & a.Row & ":U" & a.Row).Copy
                LastRow = ActiveWorkbook.Sheets("Duplicates").Range("A" & Rows.count).End(xlUp).Row + 1
                ActiveWorkbook.Sheets("Duplicates").Range("A" & LastRow).PasteSpecial
                Set c = .FindNext(c)
                If c Is Nothing Then
                    GoTo DoneFinding
                End If
            Loop While a.Address <> Firstfind
        End If
DoneFinding:
    End With
Next a

I had to change the LookIn value from xlValues to xlformulas and now it is finding the dates that I'm looking for even without using CDate.

I found this in a comment from another question. Find date value in a column VBA, the comment was by Eric K

Upvotes: 1

Related Questions