Hello there!
I'm creating a wine tool that will allow the user to type in descriptors (EXAMPLE: "cherry""blackberry") then give a list of all the wines that meet both those criteria. My formulas only give me the first match and my VBA knowledge is really rusty.
Can anyone please take a look at attached spreadsheet and give me an idea on how to accomplish this? I feel like I'm just trying random formula combinations now and getting nowhere!
Much obliged Wine list.xlsx
welcome to the forum.
Sub vbax_63620_search_multiple_crit_cells_in_column() Dim crit1 As String, crit2 As String Dim i As Long, j As Long crit1 = Range("A4").Value crit2 = Range("A5").Value j = 8 'start writing matches at row 9 in column A For i = 3 To Range("F" & Rows.Count).End(xlUp).Row If InStr(1, Range("F" & i).Value, crit1, vbTextCompare) * InStr(1, Range("F" & i).Value, crit2, vbTextCompare) > 0 Then j = j + 1 Range("A" & j).Value = Range("B" & i).Value End If Next i End Sub
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Wouldn't AutoFilter do this as it sits?
If I was only looking for one descriptor, yes. Because I need to find multiple, it doesn't workOriginally Posted by mikerickson
Thank you for the welcome and code!
It is producing results with either descriptor, I need it to give me only those with both.
Someone asked about Auto Filter which sparked an idea... Would it be possible to A2:A6 be for data entry and the macro filters the list in place with those that match ALL descriptors listed in A2:A6? Auto filter currently doesn't work due to formatting of column F
there are 16 cells in Rows 5, 6, 7, 15, 17, 26, 28, 32, 39, 57, 93, 99, 109, 129, 139, 140 of Column F which contain exact matches of both blackberry and cherry regardless of the cases.
the code i posted lists them in Colum A.
input below formula into cell I3 copy down to I145 (or as needed). you will get the same result.
Formula result 1 means related cell contains both the words in A4 and A5PHP Code:=ISNUMBER(SEARCH($A$4,F3))*ISNUMBER(SEARCH($A$5,F3))
Last edited by mancubus; 09-12-2018 at 02:14 AM.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Got it! I'll spend some time working with this this weekend, thank you so much!
Reply With Quote
