Forum Discussion
VBA Search for Multiple Criteria
Hello,
Need assistance in understanding and formulating a VBA code for multiple criteria search. I currently have a VBA Userform that searches for individual criteria and once I enter a date (currently Textbox14), click update, that search order is closed and is removed from the DashBoard worksheet. I also have Cmd Buttons of Search, Refresh, Update, Close on the Userform. Everything works perfectly.
What I want to do is expand the search for two criteria = Search Textbox1 (Date Completed, column 10) and Textbox 2 (Vendor, column 5)
The search will display the orders that match.
Display
Order Number
Textbox3
Textbox4
Textbox5
etc.
Tried searching here and YouTube but cannot find any tutorials for this
.
Thank you,
Roy
Since it is working for one criteria, then it sounds like you just need to find a way to reference a cell on the same row, but different column (I'm assuming you are searching a worksheet or structured table)?
If that is the case, then you might look at the Intersect function, which will return the intersection of a row and column range (the vba code is in Sheet1 code module).
Intersect(foundCell.entirerow, Worksheet.Range("A:A"))
Intersect(foundCell.entirerow, cell.entirecolumn)
Then you should be able to check the value of the intersection for your second criteria. I added a workbook with an example of one way you can search a worksheet range and also match criteria in another column. If it doesn't help, then you may need to add some more details regarding what you're trying to do (perhaps upload a workbook w/ sample data if it is not sensitive information).
Unfortunately, I'm working on two computers. I cannot see the attached file because this computer does not have a full suite of Excel and the other is restricted for downloads.
Below is the VBA code I'm using and working perfectly. So instead of section CommandBox1 having only one search criteria, I want to have two and the ability to list up to ten orders (TextBox 2, 3, etc) that meet that criteria. Then when I add a date in TextBox14 and click the update button, those orders are updated.
Private Sub CommandButton1_Click()
Dim Current_Status As String
Currrent_Status=Trim(TextBox1.Text)
lastrow=Worksheets("Overall").Cells(Rows.Count, 1).End(x1Up).Row
For i =2 To lastrow
If Worksheets("Overall").Cells(i, 1).Value=Current_Status Then
TextBox2.Text=Worksheets("Overall").Cells(i, 3).Value
TextBox14.Text=Worksheets("Overall").Cells(i, 11).Value
End If
Next
End Sub
-----------------------------------------------------------
Private Sub CommandButton2_Click()
Dim Current_Status As String
Currrent_Status=Trim(TextBox1.Text)
lastrow=Worksheets("Overall").Cells(Rows.Count, 1).End(x1Up).Row
For i =2 To lastrow
If Worksheets("Overall").Cells(i, 1).Value=Current_Status Then
Worksheets("Overall").Cells(i, 3).Value=TextBox2.Text
Worksheets("Overall").Cells(i, 11).Value=TextBox14.Text
End If
Next
End Sub
Sorry for the long post but I hope it provides clarity to what I want to do.
Thank you,
Roy
If I understand correctly, then I think you will need to add an AND operator to your if statement to evaluate both conditions.
But, if you want to load multiple results into separate sets of text boxes, then I think you will need a counter variable to keep a running total of number of matches and then, based on that running total, load your next match in the next set of text boxes set up to hold the results (the Select Case statement in the below example is set up for three).
However, if you think you might expand the number of matches in the future, you could use a list box with two columns to hold the matches. And, if you wanted to, you could add functions to update only matches you select from the listbox or all matches at once. I attached a screen shot as an example.
Private Sub CommandButton1_Click() Dim Current_Status As String dim count as long Current_Status=Trim(TextBox1.Text) lastrow=Worksheets("Overall").Cells(Rows.Count, 1).End(x1Up).Row For i =2 To lastrow With Worksheets("Overall") If .Cells(i, 1).Value=Current_Status And .Cells(i, 2).Value=CRITERIA2 Then count = count+1 Select Case count Case 1 TextBox2.Text = .Cells(i, 3).Value TextBox14.Text = .Cells(i, 11).Value Case 2 ' Second set of textboxes here. Case 3 ' Third Set of textboxes here, etc. End Select End If End With Next End Sub
Hey Smart Guy,
I am just new to VBA learning path. Some how i could able to create one user form with one textbox search and get the data into the listbox.
Now i have one English word power excel file. Where there will be 3 textbox. One by word search , another by synonyms search and another by antonyms search. the search can be either of any 3 textbox and the result can be display in listbox below with the words, synonyms , antonyms and many more columns say 3 more column.
The user form will will search, add, delete, or exit records.
Help of to get the understanding with the example through the blog post or youtube video.
