VBA Search for any of three items, return whole row or rows of matching data.

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124
I recently added a search button to my userform, But it does not seem to search. I am trying to use existing Text Boxs for searching by either
Customer Name, CSO# or Job # then returning any matches for possible editing. Once I get the search to work, I can try to figure out the update button. one step at a time right?
I appreciate any help available with this. Not even sure if I am using the correct type of search for the Data.


Code:
Private Sub Clearform()
End Sub
Private Sub ApperanceCheckBox_Click()
End Sub
Private Sub BRReviewCheckBox_Click()
End Sub
Private Sub BRReview_Click()
End Sub
Private Sub CancelButton_Click()
Unload Me


End Sub
Private Sub CommandButton1_Click()
    'Show User form
        UserForm1.Show


End Sub


Private Sub ClearButton_Click()


'Sub Clearform()
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next
End Sub




Private Sub UserForm1_Click()
End Sub




Private Sub UserForm1_Initialize()




End Sub
    


Private Sub CMDSearch_Click()


    Dim totRows As Long, i As Long


        totRows = Worksheets("Sheet1").Range("Ai").CurrentRegion.Rows.Count


    If Customer.Text = "" Then
    MsgBox "Enter Search Criteria"
End If


For i = 2 To totRows
    If Trim(Sheet1.Cells(i, 1)) = Trim(TextBox1.Text) Then
    Customer.Text = Sheet1.Cells(i, 1)
    CSONumber.Text = Sheet1.Cells(i, 2)
    JobNumber.Text = Sheet1.Cells(i, 3)
Exit For
End If
Next i
End Sub


Private Sub CMDUpdate_Click()


Dim EmptyRow As Long
'Make Sheet1 Active
    Sheet1.Activate


'Update Records
Answer = MsgBox("Are you sure you want to update?", vbYesNo + vbQuestion, "Update Record")
    If Answer = vbYes Then
        Cells(CurrentRow, 1).Value = Customer.Value
        Cells(CurrentRow, 2).Value = CSONumber.Value
        Cells(CurrentRow, 3).Value = JobNumber.Value
        Cells(CurrentRow, 4).Value = PCWeldType.Value
        Cells(CurrentRow, 5).Value = PCWeldGrind.Value
        Cells(CurrentRow, 6).Value = PCFinish.Value
        Cells(CurrentRow, 7).Value = NonPCWeld.Value
        Cells(CurrentRow, 8).Value = NonPCGrind.Value
        Cells(CurrentRow, 9).Value = NonPCFinish.Value
    
        If BRReview.Value = True Then Cells(CurrentRow, 10).Value = "Yes"
        If BRReview.Value = False Then Cells(CurrentRow, 10).Value = "No"
        
        If BOMReview.Value = True Then Cells(CurrentRow, 11).Value = "Yes"
        If BOMReview.Value = False Then Cells(CurrentRow, 11).Value = "No"
        
        If DimReview.Value = True Then Cells(CurrentRow, 12).Value = "Yes"
        If DimReview.Value = False Then Cells(CurrentRow, 12).Value = "No"
        
        If WeldReview.Value = True Then Cells(CurrentRow, 13).Value = "Yes"
        If WeldReview.Value = False Then Cells(CurrentRow, 13).Value = "No"
          
        If Apperance.Value = True Then Cells(CurrentRow, 14).Value = "Yes"
        If Apperance.Value = False Then Cells(CurrentRow, 14).Value = "No"
         
        If Complete.Value = True Then Cells(CurrentRow, 15).Value = "Yes"
        If Complete.Value = False Then Cells(CurrentRow, 15).Value = "No"


End If








End Sub


Private Sub OKButton_Click()
Dim EmptyRow As Long
'Make Sheet1 Active
    Sheet1.Activate


'Determine Empty Row
EmptyRow = WorksheetFunction.CountA(Range("A:A")) + 1




'Transfer Information
Cells(EmptyRow, 1).Value = Customer.Value
Cells(EmptyRow, 2).Value = CSONumber.Value
Cells(EmptyRow, 3).Value = JobNumber.Value
Cells(EmptyRow, 4).Value = PCWeldType.Value
Cells(EmptyRow, 5).Value = PCWeldGrind.Value
Cells(EmptyRow, 6).Value = PCFinish.Value
Cells(EmptyRow, 7).Value = NonPCWeld.Value
Cells(EmptyRow, 8).Value = NonPCGrind.Value
Cells(EmptyRow, 9).Value = NonPCFinish.Value


If BRReview.Value = True Then Cells(EmptyRow, 10).Value = "Yes"
If BRReview.Value = False Then Cells(EmptyRow, 10).Value = "No"


If BOMReview.Value = True Then Cells(EmptyRow, 11).Value = "Yes"
If BOMReview.Value = False Then Cells(EmptyRow, 11).Value = "No"


If DimReview.Value = True Then Cells(EmptyRow, 12).Value = "Yes"
If DimReview.Value = False Then Cells(EmptyRow, 12).Value = "No"


If WeldReview.Value = True Then Cells(EmptyRow, 13).Value = "Yes"
If WeldReview.Value = False Then Cells(EmptyRow, 13).Value = "No"
  
If Apperance.Value = True Then Cells(EmptyRow, 14).Value = "Yes"
If Apperance.Value = False Then Cells(EmptyRow, 14).Value = "No"
 
If Complete.Value = True Then Cells(EmptyRow, 15).Value = "Yes"
If Complete.Value = False Then Cells(EmptyRow, 15).Value = "No"




'Sub Clearform()
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next






End Sub




Private Sub UserForm_Click()


Call UserForm1_Initialize






End Sub


Thanks in Advance.

Bill Williamson




<colgroup><col span="2"><col><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>

</tbody>
 
First off thanks for your help,

I tried the above code. If I use Customer name, it pulls up first data with a matching name, If I search with only CSO# or Job# the form goes blank, but no error.
If I use customer and CSO# it pulls up first matching customer even if CSO# doesnt match.

Not sure what to try next.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
In post#2 you show that CSO & Job have both numeric & non numeric values. Are the numeric values actual numbers, or numbers stored as text?
 
Upvote 0
That was an excellent question, I wasn't sure so Verified. They are both formatted as "General".
Should I Change them to Text? Both CSO# and Job# will always be a combination of a letter followed by a series of Numbers. For testing purposes I didnt actually use correct #'s for either in the sample Data.

CSO#s will usually have a letter followed by 9 numbers
Job #'s Will have a letter followed by 5 numbers.

If helpful I could leave the Letter off of the Job# But on the CSo Number its an Identifier I need.

thanks,

Bill Williamson
 
Last edited:
Upvote 0
The format doesn't matter, it's the content of the cell that matters.
In an unused cell put =ISNUMBER(B2) change B2 to a cell that has a numeric value. What does the formula return.
 
Upvote 0
How about
Code:
Private Sub CMDSearch_Click()
    Dim Fnd As Range
    
    With Sheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        If Customer.Value <> "" Then .Range("A1").AutoFilter 1, Me.TextBox1.Value
        If CSONumber.Value <> "" Then .Range("A1").AutoFilter 2, Me.TextBox2.Value
        If JobNumber.Value <> "" Then .Range("A1").AutoFilter 3, Me.TextBox3.Value
        On Error Resume Next
        Set Fnd = .Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1)
        On Error GoTo 0
        If Fnd Is Nothing Then
            MsgBox "Search term not found"
        Else
            Customer.Text = Fnd.Value
            CSONumber.Text = Fnd.Offset(, 1).Value
            JobNumber.Text = Fnd.Offset(, 2).Value
            PCWeldType.Value = Fnd.Offset(, 3).Value
            PCWeldGrind.Value = Fnd.Offset(, 4).Value
            PCFinish.Value = Fnd.Offset(, 5).Value
            NonPCWeld.Value = Fnd.Offset(, 6).Value
            NonPCGrind.Value = Fnd.Offset(, 7).Value
            NonPCFinish.Value = Fnd.Offset(, 8).Value
            BRReview.Value = LCase(Fnd.Offset(, 9).Value) = "yes"
            BOMReview.Value = LCase(Fnd.Offset(, 10).Value) = "yes"
            DimReview.Value = LCase(Fnd.Offset(, 11).Value) = "yes"
            WeldReview.Value = LCase(Fnd.Offset(, 12).Value) = "yes"
            Apperance.Value = LCase(Fnd.Offset(, 13).Value) = "yes"
            Complete.Value = LCase(Fnd.Offset(, 14).Value) = "yes"
        End If
    End With
End Sub
 
Upvote 0
That was an interesting test, If I entered a number in B2 and Cell B2 was formatted as a number or general, The Formula returned a value of true, if I entered a letter and a number it gave me a false value.

If the cell B2 was formatted as text, it always returns a false vaule.

Since I am not doing any Calculations with either the CSO# or Job# , Should I treat them as Text?
 
Upvote 0
I came up with a different approach, which shouldn't matter if they are numbers or text. See code in post#25
 
Upvote 0
Thank you, That worked. I like how it actually sorted them based on search criterea, Is there a way to show all after closing the userform?
Bill Williamson
 
Upvote 0
You can either add this line as shown
Code:
        End If
        [COLOR=#ff0000].AutoFilterMode = False[/COLOR]
    End With
End Sub
to the existing code or add
Code:
 Sheets("Sheet1").AutoFilterMode = False
to a procedure that closes the form
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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