VBA - Identifying Matches from Range against Cells in another Workbook

[spidaman]

Can anyone correct my code pls as have struggled with this for a few days.

I have a range in the Active Workbook from which I want to identify matches with values in any cell in another workbook 'wb4' which has several sheets. The values from the original range include wildcard characters "?" and "*" in order to account for possible variations in the target workbook. If a match is found I'd like to format a particular cell in the corresponding row from the first workbook.

In case it helps understand what I am trying to achieve, the range from the first workbook is the result of a previous macro that has provided several variations for values in column A from the Active Workbook.

At the moment the code sticks on the MsgBox and the loop is clearly messed up.

Sub Look_Up_NOIs()

Dim cel As Range
Dim Outrng As Range
Dim Lastrow As Long
Dim wb4 As Workbook
Dim foundCell As Range
Dim Sht As Worksheet


Set ws2 = ActiveWorkbook.Sheets("Other Numbers")
Set wb4 = Workbooks("Download Contacts")
Lastrow = ws2.Range("B" & Rows.Count).End(xlUp).Row
Set Outrng = ws2.Range("I2:R" & Lastrow)


For Each cel In Outrng


    For Each Sht In wb4.Worksheets
        
            With Sht.UsedRange
            
            Set foundCell = .Cells.Find(What:=cel)
        
                If Not foundCell Is Nothing Then
                    
                    Do Until foundCell Is Nothing
                    
                    cel.Interior.ColorIndex = 3
                    cel.Font.Bold = True
                    cel.Font.ColorIndex = 1
                        
                    Set foundCell = .FindNext(foundCell)
                    
                    Loop
                    
                Else
                
                    MsgBox "NOTHING FOUND!"
                    
                End If
                
            End With
    Set foundCell = Nothing
    Next
    
Next cel
            
End Sub

Thanks in advance for any help with this.

 

[spidaman]

How do I place the Msgbox to come up only if ALL of those sheets do not have a match?

 

[Fluff]

Try

Sub Look_Up_NOIs_v2()

   Dim cel As Range
   Dim Outrng As Range
   Dim Lastrow As Long
   Dim wb4 As Workbook
   Dim foundCell As Range
   Dim NotFound As Long, i As Long
   Dim Ary As Variant
   Dim Ws2 As Worksheet, Ws3 As Worksheet
   
   Set Ws2 = ActiveWorkbook.Sheets("Other Phone Numbers")
   Set Ws3 = ActiveWorkbook.Sheets("Macro Controls & Outputs")
   Set wb4 = Workbooks("Download Contacts") ' error message 9 on this line
   
   Lastrow = Ws2.Range("A" & Rows.Count).End(xlUp).Row
   Set Outrng = Ws2.Range("I2:R" & Lastrow)
   Ary = Array("Contacts Contacts", "Calls", "Organizer Notes", "Messages SMS", "Messages MMS", "Messages Chat")
   
   For Each cel In Outrng
      For i = 0 To UBound(Ary)
         With wb4.Sheets(Ary(i)).UsedRange
   
            Set foundCell = .Cells.Find(cel.Value, , , xlPart, , , False, , False)
            
            If Not foundCell Is Nothing Then
            
               cel.Interior.ColorIndex = 3
               cel.Font.Bold = True
               cel.Font.ColorIndex = 1
            
            Else
            
               NotFound = NotFound + 1
            
            End If
            
         End With
            
         Set foundCell = Nothing
      Next i
            
   Next cel
   If NotFound = 6 Then MsgBox "Nothing found"
        
    
End Sub

 

[Fluff]

The last line should be

   If NotFound = Outrng.Count * UBound(Ary) Then MsgBox "Nothing found"

 

[spidaman]

Thanks Fluff I will test it....