VBA InStr function getting partial matches - I need exact matches - PLEASE HELP

[doispe]

Hello,

I'm new to excel, started this year with the formulas and been adventuring myself in VBA to make some of my tasks easier.

I have 2 different workbooks, one holds a column that contains a list of names, and in the other workbook, there's a column holding different name mistypes that I'm trying to find among the first workbook.

I wrote this below, after searching the internet the whole day, and for my surprise it did work! Except its considering partial matches too. What I need is to find exact words instead, but I don't know how, maybe using split or something?

I thought about adding a & " " or " " & as extra condition to the substring and it did work for some cases, but still there are situations it won't work, considering there could be a " " followed by a partial match.

Could anyone help me out on this, please? I'm so close

Sub Name_Mistypes()

    Dim str As Range
    Dim dummy As Range
    Dim s2 As Worksheet
    Dim i As Long

    Set s2 = Workbooks("Dummy Book.xlsx").Sheets("Sheet1")

    With s2
        i = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set dummy = .Range("B2:B" & i)
    End With

    For Each cell In Selection
    For Each str In dummy.Cells

        If InStr(1, cell.Value, " " & str.Value, vbTextCompare) <> 0 Or _
           InStr(1, cell.Value, str.Value & " ", vbTextCompare) Then
        cell.Interior.Color = RGB(255, 199, 206)
        End If

        Next str
        Next cell

End Sub

PS: The " " & are included in the code above for ilustration only, as it didn't work out as a solution

 

[DanteAmor]

ANTOS DA MARIA (wouldn't work because there's no " " before ANTOS)

DA MARIA ANTOS (wouldn't work because there's no " " after ANTOS)

Those cases are covered, anticipating those cases, I added to the beginning and the end " " of the value.

If InStr(1, " " & cell.Value & " "

I think all cases are covered, but if you find any, please let me know.

 

[doispe]

Can't thank you enough!!! Been the whole day struggling with this, you made it look so easy hehe

Thank you very much Dante, will sure let you know if I find out any missing cases

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.