Excel VBA InStr returns 0 although substring is valid

user_6423160 0
Viewed 311

In this code, I'm trying to check if the input entered by user (txtList) can be found in a list of data (txtList). The following code returns 0 (substring not found although "John Ho" and "Ho Tee Nee, John" is the exact same person. Can someone please enlighten me on how to resolve this? 

'code returns 0 (substring not found)
Dim txtList As String, txtInput As String
txtList = "Ho Tee Nee, John"
txtInput = "John Ho"
Debug.Print InStr(1, txtList, txtInput, vbTextCompare)
vba excel
1 Answers

Split the search criteria and look for each piece.

Dim i As Long, txtList As String, txtInput As Variant
txtList = Chr(32) & "Ho Tee Nee, John" & Chr(32)
txtInput = Split("John Ho", Chr(32))

For i = LBound(txtInput) To UBound(txtInput)
    If Not CBool(InStr(1, txtList, Chr(32) & txtInput(i) & Chr(32), vbTextCompare)) Then Exit For
Next i

If i > UBound(txtInput) Then
    Debug.Print "all parts match"
Else
    Debug.Print "incomplete match"
End If

This is a case-insensitive search. For a case-sensitive search change vbTextCompare to vbBinaryCompare.

Admin1
Related