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

B

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124
[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.


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



[/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]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
[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]Customer[/TD]
[TD]CSO#[/TD]
[TD]Job#[/TD]
[TD]PC Weld[/TD]
[TD]PC Grind[/TD]
[TD]PC Finish[/TD]
[TD]Non PC Weld[/TD]
[TD]Non PC Grind[/TD]
[TD]Non PC Finish[/TD]
[TD]BR Review[/TD]
[TD]BOM Review[/TD]
[TD]Dim Review[/TD]
[TD]Weld Review[/TD]
[TD]Overall Apperance[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD]123[/TD]
[TD]556699[/TD]
[TD]Continuous[/TD]
[TD]Smooth Not Flush[/TD]
[TD]Scoth Brite[/TD]
[TD]Continuous[/TD]
[TD]Smooth and Flush[/TD]
[TD]Scoth Brite[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]1234[/TD]
[TD]88789[/TD]
[TD]Continuous[/TD]
[TD]Smooth Not Flush[/TD]
[TD]Scoth Brite[/TD]
[TD]Continuous[/TD]
[TD]Smooth and Flush[/TD]
[TD]Scoth Brite[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]abcdd[/TD]
[TD]55468[/TD]
[TD]23457[/TD]
[TD]Continuous[/TD]
[TD]Smooth Not Flush[/TD]
[TD]Scoth Brite[/TD]
[TD]Continuous[/TD]
[TD]Smooth and Flush[/TD]
[TD]Scoth Brite[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]E1019456[/TD]
[TD]j455798[/TD]
[TD]Continuous[/TD]
[TD]Smooth Not Flush[/TD]
[TD]4B[/TD]
[TD]Intermittent[/TD]
[TD]Smooth Not Flush[/TD]
[TD]4B[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

For some reason this did not post with original thread. and I can't seem to paste a screenshot of my userform....
 
Upvote 0
How about
Code: 
Private Sub CMDSearch_Click()
    Dim Fnd As Range
    
    Set Fnd = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A:A").Find(TextBox1.Value, , , xlPart, , , False, , False)
    If Fnd Is Nothing Then
        MsgBox TextBox1.Value & " not found"
    Else
        Customer.Text = Fnd.Value
        CSONumber.Text = Fnd.Offset(, 1).Value
        JobNumber.Text = Fnd.Offset(, 2).Value
    End If
End Sub
Change sheet name in red to suit.
 
Upvote 0
I left Sheet1 the same, thats the name of the sheet its looking into. When running, I get a run-time error '424' Object required.
When Debugging it Highlights the entire line Below. I Changed "Textbox1.Value" to Customer.value it sorta worked When Searching Customer name, it populated the CSO# and the Job#, but not the rest of the data that goes with the part...Check Box's and Combo Box info ect.. if I searched for anything but Customer name the form just goes blank.


Thanks,
I really Appreciate your help with this.




Rich (BB code): 
  Set Fnd = Sheets("Sheet1").Range("A:A").Find(TextBox1.Value, , , xlPart, , , False, , False)
 
Upvote 0
Bill Williamson said:
I Changed "Textbox1.Value" to Customer.value it sorta worked When Searching Customer name, it populated the CSO# and the Job#, but not the rest of the data that goes with the part...Check Box's and Combo Box info
Click to expand...
I was basing it on your code
Code: 
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
which only populates the CSO & Job & have no knowledge of what else it should do.
if I searched for anything but Customer name the form just goes blank
Click to expand...
That's the same as your code.

Are all the CSO & Job numbers actually text, or are they a mix of text & numbers?
 
Upvote 0
The CsO# and Job # will be a mix of Numbers and letters. I added the following for the ComboBoxes and they seem to be working correctly, Now I just need to figure out the Check boxs. They come up Checked ( Light Grey) wether they have a value of true or false.

Code: 
Dim Fnd As Range
    
    Set Fnd = Sheets("Sheet1").Range("A:A").Find(Customer.Value, , , xlPart, , , False, , False)
    If Fnd Is Nothing Then
        MsgBox TextBox1.Value & " 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 = Fnd.Offset(, 9).Value        
        BOMReview.Value = Fnd.Offset(, 10).Value
        DimReview.Value = Fnd.Offset(, 11).Value
        WeldReview.Value = Fnd.Offset(, 12).Value
        Apperance.Value = Fnd.Offset(, 13).Value
        Complete.Value = Fnd.Offset(, 14).Value
 
Last edited:
Upvote 0
What is in the cells the checkboxes look at?
 
Upvote 0
The way I have it coded right now, is if it is Checked it gets a True Value, and records a "Yes" on the Data Sheet. If It is Not checked it is False and records a No on Data sheet. I then use a conditional format to Highlight any that are not yes so I know I have follow up to do. Once I am able to finish some, I then need to check the box to change it to yes and then update that record, not just save a new one. Hope That makes sense. I belive that after I get this portion working I will have to add a "next record" command button to my form, since if a search is done by Customer it may pull up multiple records for that customer. But I am so new at this, I try to work a little at a time to make sure it running properly.





Code: 
     If BRReview.Value = True Then Cells(CurrentRow, 10).Value = "Yes"
     If BRReview.Value = False Then Cells(CurrentRow, 10).Value = "No"
 
Upvote 0
That is updating the sheet, not setting the checkbox value from the sheet.
To set the checkbox try
Code: 
BRReview.Value = Fnd.Offset(, 9).Value="Yes"
 
Upvote 0
When it loads a search for document, the check boxes are all checked, light grey. It will let me uncheck them , then When I recheck them they are black like normal. So I am not sure if it is just a setting in the checkbox properties or somthing in my code. but when I hit the update button to save the record to the right line, it errors out. I get Run-Time Error '1004' Application-Defined or Object defined error. I dont think I am properly defining where it is to save the updated record to.

this is how the original data is saved-

Rich (BB code): 
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



Im wondering if Somthing like this would work?

Code: 
Dim [B][COLOR=#ff0000]Current Row[/COLOR][/B] As Long
'Make Sheet1 Active
    Sheet1.Activate


'Determine Current Row
[COLOR=#ff0000][B]Current[/B][/COLOR]Row = WorksheetFunction.CountA(Range("A:A")) + 1


'Update Information
Cells(CurrentRow, 1).Value = Customer.Value


What are your thoughts?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
572
B-radK
B
B
  • Question Question
Saving updated data record on current line vs saving new record
2 3
Replies
22
Views
3K
dmt32
D
P
  • Question Question
VBA for sending rows of data if column A = current date. (Via email)
Replies
1
Views
749
paun_shotts
P
A
  • Solved
VBA help - I can't move data to last row of different sheet
Replies
7
Views
864
vcoolio
V
A
  • Question Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
897
auglocqnuk
A

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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