VBA Findnext Issue

Question

This code works until input the coding between the ********** I'm trying to do two different searches at the same time. Can someone explain what I'm doing wrong? Thanks

Public Sub Swap()

With Sheet1.Range("A:A")
    Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Sheet1.Range("A:A").Find(what:=cusip, after:=LastCell)
If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
    account = Sheet1.Cells(FoundCell.Row, 2)

''#*************************************
    Set FoundCell2 = Sheet2.Range("B:B").Find(what:=account)
    If Not FoundCell2 Is Nothing Then
        FirstAddr2 = FoundCell2.Address
    End If

''#*********************************************

    Set FoundCell = Sheet1.Range("A:A").FindNext(after:=FoundCell)
''#Break out of loop when searched through all of the cusips
    If FoundCell.Address = FirstAddr Then
        Exit Do
    End If
Loop

End Sub

Answer 1

You can't do two different Finds at the same time. It's a limitation of Excel's object model. There's only one Find "cursor", and that will be somwhere in B:B when you try the FindNext in A:A. You have to do it the old inefficient looping way for one of the finds. Here's how you loop for the inner Find

Public Sub Swap()

    Dim LastCell As Range
    Dim FoundCell As Range
    Dim FoundCell2 As Range
    Dim FirstAddr As String
    Dim FoundAddr As String
    Dim Account As Variant

    Const CUSIP As String = "Cusip"

    Set LastCell = Sheet1.Cells(Sheet1.Rows.Count, 1)

    Set FoundCell = Sheet1.Range("A:A").Find(what:=CUSIP, after:=LastCell)
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address

        Do
            Account = Sheet1.Cells(FoundCell.Row, 2)
            FoundAddr = ""
            For Each FoundCell2 In Intersect(Sheet2.UsedRange, Sheet2.Columns(2)).Cells
                If FoundCell2.Value = Account Then
                    FoundAddr = FoundCell2.Value
                    Exit For
                End If
            Next FoundCell2

            If Len(FoundAddr) = 0 Then
                FoundAddr = "Not Found"
            End If

            Debug.Print FoundCell.Address, FoundAddr

            Set FoundCell = Sheet1.Range("A:A").FindNext(after:=FoundCell)

        Loop Until FoundCell Is Nothing Or FoundCell.Address = FirstAddr
    End If

End Sub