PDA

View Full Version : [SOLVED] Find ALL cells containing multiple words then display a corresponding cell



houkster99
09-10-2018, 01:31 PM
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. :banghead:

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 22858

mancubus
09-11-2018, 02:46 AM
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

mikerickson
09-11-2018, 06:45 AM
Wouldn't AutoFilter do this as it sits?

houkster99
09-11-2018, 10:52 AM
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 work

houkster99
09-11-2018, 11:12 AM
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

mancubus
09-12-2018, 02:01 AM
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.


=ISNUMBER(SEARCH($A$4,F3))*ISNUMBER(SEARCH($A$5,F3))
Formula result 1 means related cell contains both the words in A4 and A5

houkster99
09-13-2018, 12:02 PM
Got it! I'll spend some time working with this this weekend, thank you so much!