Wrote a Macro Search that needs approximate match instead of exact

Question

I wrote a macro to search through a filter for an exact match in column A and it needs an approximate match for a search in column B. Right now I only have it as an exact match, and I need help making it an approximate match code. Below is my code:

Sub SearchBox1()

Dim myButton As OptionButton
Dim MyVal As Long
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant

'Load Sheet into A Variable
 Set sht = ActiveSheet

'Unfilter Data (if necessary)
 On Error Resume Next
    sht.ShowAllData
 On Error GoTo 0

 'Filtered Data Range (include column heading cells)
  ActiveSheet.Range("$A50:$L$130").AutoFilter Field:=2, Criteria1:=sht.Shapes("CountrySearch").TextFrame.Characters.Text
  Exit Sub

End Sub

Thank you for your help!

In this instance you may need Criteria1:="*" & sht.Shapes("CountrySearch").TextFrame.Characters.Text & "*" — Lowpar, Jun 28 '17 at 14:10
It depends on the type of match you want: https://stackoverflow.com/questions/491148/best-fuzzy-matching-algorithm — Darren Bartrup-Cook, Jun 28 '17 at 14:26

score 1 · Answer 1 · answered Jun 28 '17 at 14:36

1

Try changing Criteria1 to this

if it's searching for a string:

Criteria1:= "*" & sht.Shapes("CountrySearch").TextFrame.Characters.Text & "*"

or, if you have numbers

Criteria1:= ">=" & sht.Shapes("CountrySearch").TextFrame.Characters.Text

answered Jun 28 '17 at 14:36

paul bica

10,557
4
23
42

Wrote a Macro Search that needs approximate match instead of exact

1 Answers1