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.
