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

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!

1 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.