VBA Find returning Nothing

[MrKowz]

I'm stumped on this one (or the coffee hasn't hit me yet)...

I have a find block of code in my macro that is always returning Nothing, even though I have verified that the record does, in fact, exist. I have checked formats, references, made sure the values both exist, but alas it keeps tripping.

What I am trying to do with this code is find a record (based on 3 conditions, Cols C, E, and G must be the same) that exists in aaa.xls to see if it is in bbb.xls. When it finds the record, if there is any differences in that row (Cols A:X), then it copies both records over to the activeworksheet. Just the .Find block is failing (the rest of the code I have verified to work).

Here is the entire code, highlighted part is where the problem is:

Public Sub ReconcileRequests()
Dim xlsxWB  As Workbook, _
    xlsxWS  As Worksheet, _
    xlsWB   As Workbook, _
    xlsWS   As Worksheet, _
    dwb     As Workbook, _
    dws     As Worksheet, _
    i       As Long, _
    j       As Long, _
    x       As Long, _
    LR      As Long, _
    dRow    As Long, _
    bool    As Boolean, _
    rng     As Range, _
    rng1    As String
    
Set xlsxWB = Workbooks("aaa.xlsx")
Set xlsWB = Workbooks("bbb.xls")
Set dwb = ActiveWorkbook
Set dws = dwb.ActiveSheet
Set xlsxWS = xlsxWB.Sheets("Actual")
Set xlsWS = xlsWB.Sheets("Actual")
LR = xlsxWS.Range("E" & Rows.Count).End(xlUp).Row
dRow = 2
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
xlsxWS.Rows(1).Copy Destination:=dws.Cells(1, 1)
For i = 2 To LR
    Application.StatusBar = "Currently checking row " & i
    bool = True
    x = 0
    If xlsxWS.Range("E" & i).Value <> vbNullString Then
        With xlsWS.Range("E:E")
            [B][COLOR=red]Set rng = .Find(xlsxWS.Range("E" & i).Value, LookIn:=xlValues)
[/COLOR][/B]            If Not rng Is Nothing Then
                rng1 = rng.Address
                Do
                    If xlsxWS.Cells(i, 3) = xlsWS.Cells(rng.Row, 3) And xlsxWS.Cells(i, 7) = xlsWS.Cells(rng.Row, 7) Then
                        x = rng.Row
                    End If
                    Set rng = .FindNext(rng)
                Loop While Not rng Is Nothing And rng.Address <> rng1 And x <> 0
            End If
        End With
        
        If Not x = 0 Then
            For j = 1 To 24
                If xlsxWS.Cells(i, j).Value <> xlsWS.Cells(x, j).Value Then
                    bool = False
                    Exit For
                End If
            Next j
        End If
        If bool = False Then
            xlsxWS.Rows(i).Copy Destination:=dws.Cells(dRow, 1)
            xlsWS.Rows(x).Copy Destination:=dws.Cells(dRow + 1, 1)
            dRow = dRow + 3
        End If
    End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
End Sub

On the first i (i=2), this should not be returning a range. I have even tested ='[aaa.xlsx]ACTUAL'!$E$2='[bbb.xls]ACTUAL'!$E$2 and that formula returns TRUE. However, as stated before, the .Find method is not seeing this.

Is there something I'm overlooking?

 

[MrKowz]

I just found the issue - the value I was using is a SSN, stored as a number without the dashes, but formatted as SSN. Same with the other sheet, it has the SSN stored without the dashes, but formatted as SSN. When I tried to do a manual find, it wasn't working either. However, when I manually inserted the dashes, it was actually finding the value.

So I decided to change both column's format to general, and whammo... the code works fine.

Can anyone shed some light as to why the number format here messed up the .Find method?

 

[Norie]

There doesn't seem to be anything obvious. Have you maybe mixed up xls and xlsx somewhere?