Search and Update Record Using Userform

Whekpop

New Member
Joined
Feb 27, 2018
Messages
8
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,993
Office Version
  1. 2007
Platform
  1. Windows
Try this:
Change this line:
VBA Code:
Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)

For this:
Rich (BB code):
Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlPart, LookIn:=xlValues)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,048
Office Version
  1. 365
Platform
  1. Windows
Just change xlWhole to xlPart & it will do a partial match
 

Whekpop

New Member
Joined
Feb 27, 2018
Messages
8
Try this:
Change this line:
VBA Code:
Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)

For this:
Rich (BB code):
Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlPart, LookIn:=xlValues)
Works perfectly... Thank you...
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,993
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,148,346
Messages
5,746,198
Members
423,998
Latest member
Nancy2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top