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

[Bill Williamson]

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. 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>

 

[Fluff]

My pleasure & thanks for the feedback

 

[Bill Williamson]

I have a couple of things on the search code that you developed that I have questions or things with, I hope you have a couple of minutes.
The Message box no longer comes up if it doesn't find a match, I not sure why but I have an Idea. I highlighted it in Red/Maroon in the code below.

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.Customer.Value
        If CSONumber.Value <> "" Then .Range("A1").AutoFilter 2, Me.CSONumber.Value
        If JobNumber.Value <> "" Then .Range("A1").AutoFilter 3, Me.JobNumber.Value
        On Error Resume Next
        Set Fnd = .Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1)
[B][COLOR=#a52a2a]On Error GoTo 0[/COLOR][COLOR=#ff0000]            '(I think this line prevents it from getting to the next two lines of code. But I am not sure)[/COLOR][/B]
        If Fnd Is Nothing Then
        MsgBox "Search term not found"   [B][COLOR=#ff0000] '(If nothing is found, nothing pops up, form goes blank.   I do not get anykind of error)[/COLOR][/B]
    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
        .AutoFilterMode = False
        
    End With
End Sub

The next question I have is regarding the Data that the search finds. Sometimes there are multiple listings with same Customer name, sometimes even the CSO# will also match. When it finds more than 1 result, it displays the first one found in list. I would like a way to go through the rest of the matching ones.

I added a Command Button Labeled as Next no code yet. Ideally it would show the next matching result if multiple are found. Im not sure if this question should be in this post or a new thread.



Thank you again for all your help,

Bill Williamson

 

[Fluff]

The "On Error GoTo 0" simply resets the error handler back to the default and needs to be kept.

Do you have any blank rows in your data?

 

[Bill Williamson]

No Blank rows, But some with only Partial Data, Such as Customer Name, CSO# and Job #.

 

[Fluff]

In that case I suspect that when the textboxes etc are blank it has fond a row with only partial data & therefore there is nothing to pull into the userform.

 

[Bill Williamson]

Thankyou for your help.

 

[Fluff]

My pleasure