Skip Mismatches maybe with Do Loop or For Next

[Rowland Hamilton]

I'm trying to pull data into a template from a source tab matching by account number. However, having trouble when the account numbers in the source tab are not in the template. If its just one account number, or they are separated by good account numbers, the code below works, but if 2 or more different bad account numbers follow each other, this does not work. Of course, if I start off with a bad account number, it also will not work:

Set FirstIO = zfir.Range("e8") 'acct number match
           IO = FirstIO.Value

    Match FirstIO to template
Set CheckIO = Range("Accounts").Find(IO, LookIn:=xlValues)

Do Until FirstIO = Empty
'misc code omitted

    'after match is found, move down next row on ZFIR
    Set FirstIO = FirstIO.Offset(1, 0)
    If FirstIO = val(CheckIO) Then
    Else

    'find next match
            IO = FirstIO.Value
            Set CheckIO = Range("Accounts").Find(IO, LookIn:=xlValues)
            If CheckIO Is Nothing Then
                'no match found, Loop thru ZFIR rows until acct matches
                i_badlines = Application.WorksheetFunction.CountIf(zfir.Range("e:e"), IO)
                Set FirstIO = FirstIO.Offset(i_badlines, 0)
                'find next match
                IO = FirstIO.Value
                Set CheckIO = Range("Accounts").Find(IO, LookIn:=xlValues)

            Else
                   'misc code
            End If

       End if
Loop

How do I fix this Tried looping until CheckIO is Not Nothing but could not get the proper syntax to make that Do Loop (of course would also have to make sure it wouldn't loop infinitely, perhaps count total rows between current source tab row and last source tab row as max iterations with in loop:
iterations = LastRow - FirstIO.Row

COmplicated, I know. Think I'm making t too hard. Have to skip bad accounts to get to good ones below, if there are any. Will sort to keep same account numbers together in source tab.

Thank you,
Rowland

 

[Rowland Hamilton]

Got this part to work, now need to add code in case I start with a bad account number...

If CheckIO Is Nothing Then
                For i_badrun = 1 To (i_zfirRows - i_zfirStartrow)
                'no match found, Loop thru ZFIR rows until acct matches
                    i_badlines = Application.WorksheetFunction.CountIf(zfir.Range("e:e"), IO)
                    Set FirstIO = FirstIO.Offset(i_badlines, 0)
                    'find next match
                    IO = FirstIO.Value
                    Set CheckIO = Range("Accounts").Find(IO, LookIn:=xlValues)

                    If CheckIO Is Nothing Then
                    Else
                        Exit For
                    End If
                
                Next i_badrun
                
            Else
            End If

 

[tweedle]

Try this for your loop.

    With zfir
        Set inputRng = .Range("e8:e" & .Cells(.Rows.Count, 5).End(xlUp).Row)
    End With


    For Each c In inputRng
        Set FirstIO = c
        'acct number match
        IO = FirstIO.Value


        Set CheckIO = Sheets("Accounts").UsedRange.Find(IO, LookIn:=xlValues)
        If CheckIO Is Nothing Then
            'no match found, Loop thru ZFIR rows until acct matches
            i_badlines = Application.WorksheetFunction.CountIf(zfir.Range("e:e"), IO)
            'find next match
        Else
            'found a match
            'misc code
        End If
    Next