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

[Bill Williamson]

[TABLE="width: 1444"]
<colgroup><col span="2"><col><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] 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



[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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