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.

 

[NdNoviceHlp]

Set foundCell = .FindNext(cel)

HTH. Dave

 

[spidaman]

Thanks for your suggestion. I've tried changing that line but the code is still not working

 

[Fluff]

Firstly, there is no need for the Do Loop, as it will simply highlight the same cell repeatedly.
Secondly can you give some examples of the values in OutRng and the values in the other workbook?

 

[spidaman]

Firstly, there is no need for the Do Loop, as it will simply highlight the same cell repeatedly.

OK thanks will remove the Do Loop.

The Outrng has values such as:

*956?034*
*8375?33*
*83753?3*

With a similar type string in each cell of this range. The wildcard characters are to account for possible variations of 1 or more characters at either end of or within the string.

wb4 has a huge variety of text/numeric strings in cells across several worksheets. This code is something i would be using regularly where the content of wb4 can vary a lot.

 

[Fluff]

Try

For Each cel In Outrng


    For Each Sht In wb4.Worksheets
        
            With Sht.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
                
                    MsgBox "NOTHING FOUND!"
                    
                End If
                
            End With
    Set foundCell = Nothing
    Next
    
Next cel
            
End Sub

 

[spidaman]

With this code, when the Else clause is triggered when nothing is found the code sticks. The MsgBox appears but when you click 'OK' the MsgBox does not go away and I have to force close Excel.

Any ideas?

 

[Fluff]

Does the msgbox disappear & reappear very quickly?

 

[spidaman]

Yes exactly

 

[Fluff]

In that case your macro hasn't finished.
How many cells are there in OutRng?
And how many sheets in Wb4?