Forum Discussion
VBA Search for Multiple Criteria
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 SubSorry for the delay in responding. Things are hectic. Getting close to finishing this project using your examples and code. Thanks for your assistance.
I have recreated the Userform minus the graphics on the Search and Update buttons.
For the coding just need some clarification: Criteria 1 = TextBox1 (from column 6 of Overall worksheet). Therefore, in TextBox1 the criteria will be Completed / Yes or Completed / No or can I just type Completed with a wildcard in the code to recognize any text after Completed? For Criteria 2 = Textbox2 (from column 5 of Overall worksheet) the criteria will be a vendor like ABC, XYZ, etc.
Click Search and the Results will populate in the List Box?
I only need one TextBox in Update. So TextBox3 is the date (for column 11 of Overall worksheet) that will close the orders found from the criteria. Adjusted the UserForm for this.
When I run DeBug, getting error - End If without block if
Again, thank you for your assistance.
You could add a wildcard option for Criteria 1. One way would be to add a checkbox to enable wildcards, which controls a module variable. Then, the search function could check that module variable value and either match the text exactly or compare the text using the Like operator.
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator
I made the change to the example workbook and included comments where I made changes.
The error you noted appears to be an "If/Then" statement that is missing an "End If." You'll need to check your If/Then blocks and make sure there is a matching End If. I'd have to see the code to be more helpful than that.
The List Box looks to be the solution I'm searching for.
I will attempt to recreate your example.
Thank you.
