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 stringFashion*
therefore matchesFashions*
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: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.