Userform to Search and Update (Follow On)

markw1804

New Member
Joined
Nov 19, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Good morning,
I know this is relation to an older post (User form to Search for and Update Data) but it has almost the perfect solution for me, I am also finding code I need through searches etc. I just wanted to ask from others who would know better is it possible to make this Search code search all columns?
I would like the textbox to search all columns for any matches? Or if possible to list the columns?
I gather from testing it seems to use the column specified here to search on "Const SearchCol As Integer = "Column No.""
I have seen postings on FindAll function but not sure how to adapt it so that the results then show up in the Userform similar to this one?
I'm sure its straightforward but I'm running into a brick wall trying to adapt the code? I cant get my head round it.
Any pointers would be very much appreciated.
Thanks
Mark

I have copied the code I think I need to change:

VBA Code:
Private Sub cmdSearch_Click()

Dim wsStock As Worksheet
Dim Description As String, strSearch As String
Dim FoundRecord As Range
Dim RecordNo As Integer
Const SearchCol As Integer = 9

    Dim MatchCount As Integer
    
    With wsStock
'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 Description <> 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.cmdSearch.Caption = "Find Next"
'enable update button
                Me.cmdUpdate.Enabled = True
'mark first matched record address
                If Len(Description) = 0 Then Description = FoundRecord.Address
                
            Else
'no more matches
                MsgBox strSearch & Chr(10) & Space(20) & Chr(10) & "End Of Search", 48, "End Of Search"
            End If
        Else
'no match found
            MsgBox strSearch & Chr(10) & " No Records Found", 48, "Not Found"
        End If
    End With
End Sub
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,127,680
Messages
5,626,247
Members
416,168
Latest member
DROP_DATABASE_MrExel

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