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]

Hi and welcome to MrExcel.

If you want exact matches, try the following:

If LCase(cell.Value) = LCase(Str.Value) Then
        cell.Interior.Color = RGB(255, 199, 206)

 

[doispe]

Hey Dante

Thanks for the reply!

So for this workaround I'll need everything to be either lowercase or uppercase?

 

[DanteAmor]

Not necessarily, you can solve it using LCase on both data, just like you were doing with the vbTextCompare parameter.

 

[doispe]

I don't understand how this could work. I tried changing everything to lowercase and ran the code, but nothing happens
How could this determine whether a cell has a text as a substring?

For instance:

ANTOS = substring to be found

MARIA DOS SANTOS (string I'm looking into)

In my situation, SANTOS will be considered a match, but what I need is to match only if its like this:

MARIA DOS ANTOS

 

[doispe]

I'm sorry, it actually worked!!! I'm checking if there's any other issues, but thank you very much!!!

Can you explain me the logic?

 

[doispe]

It works only if there's a single word in the string

 

[DanteAmor]

Try this:

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

If you have a case that doesn't work, it would be nice if you post it here for review.

 

[doispe]

Hey, it seems to have worked, but I have to go through the list and investigate if there's something missing.

I suspect it will only work if there are blanks before and after each word, is that right? If that's the case, wouldn't it work only with 3 words string?

For instance:

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

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

MARIA ANTOS DA (would work as there're " " before and after ANTOS)

 

[doispe]

Well I guess I just had to use the same example above to test lol

It did work! But do you think there might happen something like that, as we're dealing with " "?

It's a 100k row document that I'm looping through, so I'll investigate in detail, but if you got any clue of what this could not cover as a solution, let me know please, so I know what to look into