VBA coding for Search/Search Next (same identifier) command button with Loop?

TitusandNero

TitusandNero

New Member
Joined
Mar 17, 2016
Messages
26
Hi there,
From watching YouTube videos and reading advice on this website I've managed to build myself a pretty snazzy database with a multi-page Userform. It's all working perfectly well however, I have one command button left to code and I can't find anything on the net that does what I want it to do. On the Userform is a 'Text Box' and next to that is a 'Search' Command Button - enter in the unique ID in the text box, click the search command button and hey presto it populates the Userform. All good so far. However, I would like to give the end user another search option, this time by Membership Number, just in case they can't remember the unique ID. The problem I'm having is that there maybe more than one entry under that Membership Number. I'm not sure of the terminology but I'm guessing I need a 'loop' in my coding? So that every time I click on this 'Search' command button it goes to the next record with that Membership Number, and the next , and the next, looping back on itself to the start. If there is only one record found then I also want it to pop up a message box saying something like 'No additional records found'. Can someone help me please? Here is my coding for the Search command button:

Private Sub cmdSMN1_Click()
txbFN1.Text = ""
txbSN1.Text = ""
txbMN1.Text = ""
txbTN1.Text = ""
txbEM1.Text = ""
txbEMP1.Text = ""
txbDEPT1.Text = ""
txbCN1.Text = ""
txbCD1.Text = ""
txbCC1.Text = ""
cbxREPTEL1.Text = ""
txbNOTES1.Text = ""
cxbxET1.Value = "False"
cxbxDC1.Value = "False"
cxbxPI1.Value = "False"
cmdCLR1.Caption = "Clear"
TRows = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
For i = 2 To TRows
If Val(Strings.Trim(Worksheets("Data").Cells(i, 2).Value)) = Val(Strings.Trim(tbMN1.Text)) Then

txbFN1.Text = Worksheets("Data").Cells(i, 4).Value
txbSN1.Text = Worksheets("Data").Cells(i, 3).Value
txbMN1.Text = Worksheets("Data").Cells(i, 2).Value
LDD1.Date = Worksheets("Data").Cells(i, 5).Value
txbTN1.Text = Worksheets("Data").Cells(i, 6).Value
txbEM1.Text = Worksheets("Data").Cells(i, 7).Value
txbEMP1.Text = Worksheets("Data").Cells(i, 8).Value
txbDEPT1.Text = Worksheets("Data").Cells(i, 9).Value
txbCN1.Text = Worksheets("Data").Cells(i, 1).Value
lbxCT1.Value = Worksheets("Data").Cells(i, 10).Value
txbCD1.Value = Worksheets("Data").Cells(i, 11).Value
LDD2.Date = Worksheets("Data").Cells(i, 12).Value
LDD3.Date = Worksheets("Data").Cells(i, 13).Value
txbCC1.Text = Worksheets("Data").Cells(i, 14).Value
cbxREPTEL1.Text = Worksheets("Data").Cells(i, 15).Value
cxbxET1.Value = Worksheets("Data").Cells(i, 16).Value
cxbxDC1.Value = Worksheets("Data").Cells(i, 17).Value
cxbxPI1.Value = Worksheets("Data").Cells(i, 18).Value
txbNOTES1.Text = Worksheets("data").Cells(i, 19).Value
tbSN1.Value = ""
tbCN1.Value = ""
Exit For
End If
Next i
If txbMN1.Text = "" Then
End If
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
  • Is this the code for search for ID?
  • Is there a separate command button or Option buttons to configure if the search is for an ID versus Membership Number?
  • What column are the Membership Numbers?
 
Upvote 0
Hi, thanks for responding, total newb at this. This is the code for search Membership Number (MN). I have a similar code for the ID. Each has there own text box and command button for searching on the userform . The column for Membership Number is 2 or B. Cheers.
 
Upvote 0
Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdSMN1_Click()
    
    [color=darkblue]Static[/color] rngFound [color=darkblue]As[/color] Range
    [color=darkblue]Static[/color] strSearch [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]If[/color] tbMN1.Text = "" [color=darkblue]Then[/color]
        MsgBox "Missing search term. ", , "Invalid Entry"
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    txbFN1.Text = ""
    txbSN1.Text = ""
    txbMN1.Text = ""
    txbTN1.Text = ""
    txbEM1.Text = ""
    txbEMP1.Text = ""
    txbDEPT1.Text = ""
    txbCN1.Text = ""
    txbCD1.Text = ""
    txbCC1.Text = ""
    cbxREPTEL1.Text = ""
    txbNOTES1.Text = ""
    cxbxET1.Value = "False"
    cxbxDC1.Value = "False"
    cxbxPI1.Value = "False"
    cmdCLR1.Caption = "Clear"
    
    [color=darkblue]If[/color] rngFound [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Set[/color] rngFound = Worksheets("Data").Range("B1")           [color=green]'If first search, start at top[/color]
    [color=darkblue]If[/color] strSearch <> Trim(tbMN1.Text) [color=darkblue]Then[/color] rngFound = Worksheets("Data").Range("B1")     [color=green]'If new search, start at top[/color]
    strSearch = Trim(tbMN1.Text)
    
    [color=darkblue]Set[/color] rngFound = Worksheets("Data").Columns("B").Find(What:=strSearch, _
                                                        After:=rngFound, _
                                                        LookIn:=xlValues, _
                                                        LookAt:=xlWhole, _
                                                        SearchOrder:=xlByRows, _
                                                        SearchDirection:=xlNext, _
                                                        MatchCase:=False)
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] rngFound [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        
        i = rngFound.Row
        
        txbFN1.Text = Worksheets("Data").Cells(i, 4).Value
        txbSN1.Text = Worksheets("Data").Cells(i, 3).Value
        txbMN1.Text = Worksheets("Data").Cells(i, 2).Value
        LDD1.Date = Worksheets("Data").Cells(i, 5).Value
        txbTN1.Text = Worksheets("Data").Cells(i, 6).Value
        txbEM1.Text = Worksheets("Data").Cells(i, 7).Value
        txbEMP1.Text = Worksheets("Data").Cells(i, 8).Value
        txbDEPT1.Text = Worksheets("Data").Cells(i, 9).Value
        txbCN1.Text = Worksheets("Data").Cells(i, 1).Value
        lbxCT1.Value = Worksheets("Data").Cells(i, 10).Value
        txbCD1.Value = Worksheets("Data").Cells(i, 11).Value
        LDD2.Date = Worksheets("Data").Cells(i, 12).Value
        LDD3.Date = Worksheets("Data").Cells(i, 13).Value
        txbCC1.Text = Worksheets("Data").Cells(i, 14).Value
        cbxREPTEL1.Text = Worksheets("Data").Cells(i, 15).Value
        cxbxET1.Value = Worksheets("Data").Cells(i, 16).Value
        cxbxDC1.Value = Worksheets("Data").Cells(i, 17).Value
        cxbxPI1.Value = Worksheets("Data").Cells(i, 18).Value
        txbNOTES1.Text = Worksheets("data").Cells(i, 19).Value
        tbSN1.Value = ""
        tbCN1.Value = ""
        
        [color=darkblue]If[/color] Worksheets("Data").Columns("B").FindNext(After:=rngFound).Address = rngFound.Address [color=darkblue]Then[/color]
            MsgBox "No additional records found.", vbInformation, "One Entry Found"
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
    [color=darkblue]Else[/color]
        MsgBox Trim(tbMN1.Text), vbExclamation, "No Match Found"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
UPDATE

I found an error in the code above, use this instaed.

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdSMN1_Click()
    
    [COLOR=darkblue]Static[/COLOR] rngFound [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Static[/COLOR] strSearch [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] tbMN1.Text = "" [COLOR=darkblue]Then[/COLOR]
        MsgBox "Missing search term. ", , "Invalid Entry"
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    txbFN1.Text = ""
    txbSN1.Text = ""
    txbMN1.Text = ""
    txbTN1.Text = ""
    txbEM1.Text = ""
    txbEMP1.Text = ""
    txbDEPT1.Text = ""
    txbCN1.Text = ""
    txbCD1.Text = ""
    txbCC1.Text = ""
    cbxREPTEL1.Text = ""
    txbNOTES1.Text = ""
    cxbxET1.Value = "False"
    cxbxDC1.Value = "False"
    cxbxPI1.Value = "False"
    cmdCLR1.Caption = "Clear"
    
    [COLOR=darkblue]If[/COLOR] rngFound [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Set[/COLOR] rngFound = Worksheets("Data").Range("B1")           [COLOR=green]'If first search, start at top[/COLOR]
    [COLOR=darkblue]If[/COLOR] strSearch <> Trim(tbMN1.Text) Then [COLOR=red]Set[/COLOR] rngFound = Worksheets("Data").Range("B1") [COLOR=green]'If new search, start at top[/COLOR]
    strSearch = Trim(tbMN1.Text)
    
    [COLOR=darkblue]Set[/COLOR] rngFound = Worksheets("Data").Columns("B").Find(What:=strSearch, _
                                                        After:=rngFound, _
                                                        LookIn:=xlValues, _
                                                        LookAt:=xlWhole, _
                                                        SearchOrder:=xlByRows, _
                                                        SearchDirection:=xlNext, _
                                                        MatchCase:=False)
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngFound [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        
        i = rngFound.Row
        
        txbFN1.Text = Worksheets("Data").Cells(i, 4).Value
        txbSN1.Text = Worksheets("Data").Cells(i, 3).Value
        txbMN1.Text = Worksheets("Data").Cells(i, 2).Value
        LDD1.Date = Worksheets("Data").Cells(i, 5).Value
        txbTN1.Text = Worksheets("Data").Cells(i, 6).Value
        txbEM1.Text = Worksheets("Data").Cells(i, 7).Value
        txbEMP1.Text = Worksheets("Data").Cells(i, 8).Value
        txbDEPT1.Text = Worksheets("Data").Cells(i, 9).Value
        txbCN1.Text = Worksheets("Data").Cells(i, 1).Value
        lbxCT1.Value = Worksheets("Data").Cells(i, 10).Value
        txbCD1.Value = Worksheets("Data").Cells(i, 11).Value
        LDD2.Date = Worksheets("Data").Cells(i, 12).Value
        LDD3.Date = Worksheets("Data").Cells(i, 13).Value
        txbCC1.Text = Worksheets("Data").Cells(i, 14).Value
        cbxREPTEL1.Text = Worksheets("Data").Cells(i, 15).Value
        cxbxET1.Value = Worksheets("Data").Cells(i, 16).Value
        cxbxDC1.Value = Worksheets("Data").Cells(i, 17).Value
        cxbxPI1.Value = Worksheets("Data").Cells(i, 18).Value
        txbNOTES1.Text = Worksheets("data").Cells(i, 19).Value
        tbSN1.Value = ""
        tbCN1.Value = ""
        
        [COLOR=darkblue]If[/COLOR] Worksheets("Data").Columns("B").FindNext(After:=rngFound).Address = rngFound.Address [COLOR=darkblue]Then[/COLOR]
            MsgBox "No additional records found.", vbInformation, "One Entry Found"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]Else[/COLOR]
        MsgBox Trim(tbMN1.Text), vbExclamation, "No Match Found"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
Hi Alpha Frog. Have a bug in my database I'm hoping you can help me with. The code you gave me above worked perfectly yet I've encountered a problem when I apply it to another search criteria. I'll try my best to explain the problem. When I press Search Membership Number on the code above and there is no match I get a msgbox stating the same even if I keep pressing the search button it'll keep pinging back the msgbox 'no match found'. Now when I apply the code to search for a surname and there is no match in the first instance I'll get a msgbox stating 'no match found' but if I press it again the system flags up a debug and highlights a bit of your coding which I don't understand. Any clue what I've done wrong?

Rich (BB code): 
Private Sub CommandButton5_Click()
 Static rngFound As Range
    Static strSearch As String
    Dim i As Long
    
    If tbSN2.Text = "" Then
        MsgBox "Missing search term. ", , "Invalid Entry"
        Exit Sub
    End If
                tbCN2.Text = ""
                tbFN3.Text = ""
                tbSN3.Text = ""
                tbMN3.Text = ""
                tbTEL3.Text = ""
                tbEL3.Text = ""
                cbEMP3.Text = ""
                tbDEPT3.Text = ""
                tbBD3.Text = ""
                cbRTEL3.Text = ""
                cbxDC3.Value = "False"
                cbxET3.Value = "False"
                tbCNt3.Text = ""
                TBCC3.Text = ""
                TBDJ3.Text = ""
                TBDI3.Text = ""
                TBCO3.Text = ""
    
    If rngFound Is Nothing Then Set rngFound = Worksheets("Data").Range("B1")           'If first search, start at top
    If strSearch <> Strings.Trim(tbSN2.Text) Then Set rngFound = Worksheets("Data").Range("C1") 'If new search, start at top
    strSearch = Strings.Trim(tbSN2.Text)
    
    Set rngFound = Worksheets("Data").Columns("C").Find(What:=strSearch, _
                                                        After:=rngFound, _
                                                        LookIn:=xlValues, _
                                                        LookAt:=xlWhole, _
                                                        SearchOrder:=xlByRows, _
                                                        SearchDirection:=xlNext, _
                                                        MatchCase:=False)    
    If Not rngFound Is Nothing Then
        
        i = rngFound.Row
        tbCN2.Text = Worksheets("Data").Cells(i, 1).Value
        tbMN3.Text = Worksheets("Data").Cells(i, 2).Value
        tbMN2.Text = Worksheets("Data").Cells(i, 2).Value
        tbSN3.Text = Worksheets("data").Cells(i, 3).Value
        tbFN3.Text = Worksheets("Data").Cells(i, 4).Value
        TBDJ1.Text = Worksheets("Data").Cells(i, 5).Value
        tbTEL3.Text = Worksheets("Data").Cells(i, 6).Value
        tbEL3.Text = Worksheets("Data").Cells(i, 7).Value
        cbEMP3.Text = Worksheets("Data").Cells(i, 8).Value
        tbDEPT3.Text = Worksheets("Data").Cells(i, 9).Value
        lbxCT3.Value = Worksheets("Data").Cells(i, 10).Value
        tbBD3.Text = Worksheets("Data").Cells(i, 11).Value
        TBDI1.Text = Worksheets("Data").Cells(i, 12).Value
        TBCO1.Text = Worksheets("Data").Cells(i, 13).Value
        TBCC3.Text = Worksheets("Data").Cells(i, 14).Value
        cbRTEL3.Text = Worksheets("Data").Cells(i, 15).Value
        cbxDC3.Value = Worksheets("Data").Cells(i, 16).Value
        cbxET3.Value = Worksheets("Data").Cells(i, 17).Value
        tbCNt3.Value = Worksheets("Data").Cells(i, 18).Value
        
        If Worksheets("Data").Columns("C").FindNext(After:=rngFound).Address = rngFound.Address Then
            MsgBox "No additional records found.", vbInformation, "One Entry Found"
        End If
        
    Else
        MsgBox Strings.Trim(tbMN3.Text), vbExclamation, "No Match Found"
    End If
End Sub
 
Upvote 0
Sorry Alpha Frog this is a case of Sods Law - I've been looking at this piece of code for 2 weeks trying to find the error and as soon as I post it up I see it staring me right in the face. Apologies.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA Search box and button code
Replies
0
Views
247
jlcodes
J
M
  • Question Question
search in worksheet for multiple words via userform
Replies
6
Views
475
Max01
M
S
  • Solved
have userform textbox run macro without using command button
Replies
5
Views
1K
Bosquedeguate
Bosquedeguate
S
  • Question Question
VBA Code- finding #Spill and moving the formula to the next column
Replies
0
Views
807
stromnoexcel
S
J
  • Question Question
Data Transfer from UserForm using Radio Buttons in Frames
Replies
2
Views
540
JaesunB
J

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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
Back
Top