Search and Update Record Using Userform

[Whekpop]

I found the VBA code below on this forum and it has served me excellently. However, I would like to search using wildcard (*) in my search string. For instance, if I am searching for TRISORTIUM, I could enter TRI, or TIUM or any combination and still retrieve my data. Can the code be tweaked to do that? I need someone to kindly look at and assist. Thank you.

Dim wsGoalTracking As Worksheet
Dim FirstAddress As String, strSearch As String
Dim FoundRecord As Range
Dim RecordNo As Integer
Const SearchCol As Integer = 2


Private Sub Search_Click()
    Dim MatchCount As Integer
   
    With wsGoalTracking
'search range for strSearch match
        Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)
        If Not FoundRecord Is Nothing Then
'count number matches in range
        MatchCount = Application.CountIf(.Columns(SearchCol), strSearch)
       
            If FirstAddress <> FoundRecord.Address Then
                RecordNo = RecordNo + 1
'update caption
                Me.Caption = "Search Match " & RecordNo & " of " & MatchCount
'display found record
                GetRecord Me, FoundRecord
'if more than one match in range change Search Button caption
                If MatchCount > 1 Then Me.Search.Caption = "Find Next"
'enable update button
                Me.UpdateData.Enabled = True
'mark first matched record address
                If Len(FirstAddress) = 0 Then FirstAddress = FoundRecord.Address
               
            Else
'no more matches
                MsgBox strSearch & Chr(10) & Space(20) & Chr(10) & "End Of File", 48, "End Of File"
            End If
        Else
'no match found
            MsgBox strSearch & Chr(10) & " Record Not Found", 48, "Not Found"
        End If
    End With
End Sub


Private Sub TextBox1_Change()
    SearchReset Me.TextBox1.Text
End Sub


Private Sub UpdateData_Click()
'update record
    GetRecord Me, FoundRecord, xlAdd
'inform user
    MsgBox strSearch & Chr(10) & " Record Updated", 48, "Record Updated"
End Sub
Sub GetRecord(ByVal Form As Object, ByVal Target As Range, Optional ByVal Action As Integer)
    Dim FormControl As Variant
    Dim i As Integer
    With Form
    For Each FormControl In Array(.TextBox5, .TextBox2, .ComboBox1, .TextBox3, .TextBox4)
    i = i + 1
    If Action = xlAdd Then
'add record to worksheet
        Target.Offset(0, Choose(i, -1, 1, 2, 3, 4)).Value = FormControl.Value
    Else
'get record from worksheet
        FormControl.Value = Target.Offset(0, Choose(i, -1, 1, 2, 3, 4)).Value
    End If
    Next
    End With
End Sub


Sub SearchReset(Optional ByVal Text As String)
'reset commnandbutton controls
    With Me.Search
        .Caption = "Find"
        .Enabled = CBool(Len(Text) > 0)
    End With
   
    With Me.UpdateData
        .Caption = "Update"
        .Enabled = False
    End With
   
'reset variables
    strSearch = Text
    FirstAddress = ""
    RecordNo = 0
    Set FoundRecord = Nothing
    Set FoundRecord = wsGoalTracking.Cells(1, SearchCol)
'reset caption
    Me.Caption = "Search"
End Sub


Private Sub UserForm_Initialize()
    Set wsGoalTracking = ThisWorkbook.Worksheets("GoalTracking")
    SearchReset
End Sub

 

[DanteAmor]

Try this:
Change this line:

Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)

For this:

Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlPart, LookIn:=xlValues)

 

[Fluff]

Just change xlWhole to xlPart & it will do a partial match

 

[Whekpop]

Try this:
Change this line:

Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)

For this:

Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlPart, LookIn:=xlValues)

Works perfectly... Thank you...

 

[Whekpop]

Just change xlWhole to xlPart & it will do a partial match

Works perfectly... Thank you.

 

[DanteAmor]

Glad we could help & thanks for the feedback