Exit Loop if `all Matches` are found on specific sheets-CodePudding

Using below code to Match a range on sheet "Auto" against a range on specific sheets on the same workbook.
If a match found, then some values will be inserted on that specific Sheets.
Practically, There will not be any duplicate matches, so I need to exit the main Loop if all matches are found.
e.g values 111 and 222 are found on sheets "North" and sheet "central" receptively,
So there is no need to search for a match on the other sheets,so the need to exit loop.
in advance,grateful for any useful help comment and answers.

Option Explicit
Option Compare Text

    Sub Run_Modified()
    
         Dim Auto_Data As Range: Set Auto_Data = Sheets("Auto").Range("A2:A21")
             
         Dim ws As Worksheet
    For Each ws In Sheets(Array("North", "Central", "Onshore", "South"))  'exit this Loop if all below Match are found
       
         Dim WorkOrder As Range: Set WorkOrder = ws.Range("B3:B250")
    
         Dim cell As Object, Match_A As Variant
         For Each cell In WorkOrder
     Match_A = Application.Match(cell.value, Auto_Data, 0)
          If Not IsError(Match_A) Then
             cell.Offset(, 6).Resize(1, 3).value = Array("Close", Now, ws.name)
          End If
               
        Next cell
        
     Next ws 'I need to exit this Loop if all Matches are found
    
    End Sub

CodePudding user response：

Exit For is your friend.

If I understand you correctly this should be the part where you have to insert it:

If Not IsError(Match_A) Then
    cell.Offset(, 6).Resize(1, 3).value = Array("Close", Now, ws.name)
    exit for
End If

If there is a match on the sheet, data will be inserted and the For each cell-loop will be exited.

Next sheet will be handled.

CodePudding user response：

Please, test the next adapted code. It loads in an array the matched values and skip from next match checking if already in the array. If the array has been loaded with all values of Auto_Data range, both loops are exited:

    Sub Run_Modified()
         Dim Auto_Data As Range: Set Auto_Data = Sheets("Auto").Range("A2:A21")
         Dim arr_MatchOK: ReDim arr_MatchOK(Auto_Data.rows.count - 1) 'redim the array to keep matched cells value
         
         Dim ws As Worksheet, WorkOrder As Range, k As Long, mtch
         Dim cell As Range, Match_A As Variant
         For Each ws In Sheets(Array("North", "Central", "Onshore", "South"))  'exit this Loop if all below Match are found
              Set WorkOrder = ws.Range("B3:B250")
        
              For Each cell In WorkOrder
                    'checking if already in the matched values array:
                    mtch = Application.match(cell.value, arr_MatchOK, 0)
                    If IsError(mtch) Then 'if not a match:
                        Match_A = Application.match(cell.value, Auto_Data, 0)
                        If Not IsError(Match_A) Then
                           cell.Offset(, 6).Resize(1, 3).value = Array("Close", Now, ws.Name)
                           arr_MatchOK(k) = cell.value: k = k   1   'place the value in the a rray
                           If k > UBound(arr_MatchOK) Then Exit Sub 'check if all values have been loaded
                                                                    'if yes, exit
                        End If
                    End If
             Next cell
        Next ws
    End Sub