Skip Mismatches maybe with Do Loop or For Next

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
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:

Code:
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
Got this part to work, now need to add code in case I start with a bad account number...
Code:
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

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Try this for your loop.

Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,122
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top