Find isn’t finding exact match in a string in excel

findmicrosoft excelvba

I wrote a code in vba in which if I enter a string in cell B8 then it will match in other worksheet ranged A6:A500. My finding text is Carlos Leather Fashion*Lefties*Alex*Mid Blue. And there is a string in A6 which is Carlos Leather Fashions*Lefties*Alex*Mid Blue. So, it is not matching because there is an extra 's' in fashion abbreviation. But my vba code is showing Found in this case also. Here is my code –

Dim helper As String
    
helper = Cells(8, 2).Value
    
Dim paymentStatusSheet As Worksheet
Set paymentStatusSheet = Worksheets("Payment-Summary")
'--------------------------------------------------------------Set the Payment status worksheet
    
If paymentStatusSheet.Range("A6:A500").Find(What:=helper, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
    
    MsgBox "Not Found"
    
Else
    MsgBox "Found"
    
End If

Help pls!

Best Answer

The problem arise because of Range.Find() seeing the asterisk * character as wildcard, which match any characters. The search string Fashion* therefore matches Fashions*

A quick solution is to escape the search pattern. Excel treats ~* in the search string as a single asterisk character. Therefore a quick fix to your specific use case would be:

Dim helper As String
    
helper = Cells(8, 2).Value
helper = Replace(helper, "*", "~*")  '<-- Note this new line
    
Dim paymentStatusSheet As Worksheet
Set paymentStatusSheet = Worksheets("Payment-Summary")
'--------------------------------------------------------------Set the Payment status worksheet
    
If paymentStatusSheet.Range("A6:A500").Find(What:=helper, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
    
    MsgBox "Not Found"
    
Else
    MsgBox "Found"
    
End If

Here, I did not take care of other wildcards, i.e. ? and ~. You may need to escape those if you foresee them in your search strings.

Related Question