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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.