Microsoft Excel - Skip Row if No Match, Do Loop and For Next Syntax

Asked By Rowland Hamilton on 11-Feb-13 11:29 PM
Folks:

I got this part to work, now I just need to modify for when 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
[/CODE]

Original Question:

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[/CODE]

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