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]

 

[Bill Williamson]

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.

 

[Fluff]

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?

 

[Bill Williamson]

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

 

[Fluff]

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.

 

[Fluff]

How about

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

 

[Bill Williamson]

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?

 

[Fluff]

I came up with a different approach, which shouldn't matter if they are numbers or text. See code in post#25

 

[Bill Williamson]

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

 

[Fluff]

You can either add this line as shown

        End If
        [COLOR=#ff0000].AutoFilterMode = False[/COLOR]
    End With
End Sub

to the existing code or add

 Sheets("Sheet1").AutoFilterMode = False

to a procedure that closes the form

 

[Bill Williamson]

That worked well. Thank you for your time and help with this. Moving on to another issue.

Bill Williamson