Forum Discussion
How do I find multiple search terms in Excel?
AbtinS I see my code only looks for cells which contain just the word you are looking for. This adjusted version looks for cells containing the words:
Option Explicit Sub ListHits() Dim Cell2Find As Range Dim FoundCell As Range Dim FirstFound As Range Dim Ct As Long Dim KeepLooking As Boolean For Each Cell2Find In Worksheets("Keywords").UsedRange If Len(Cell2Find.Value) > 0 Then Set FirstFound = Nothing With Worksheets("Data") Ct = 0 Set FoundCell = Nothing Set FoundCell = .UsedRange.Find(What:=Cell2Find.Value, After:=.Range("A1"), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then KeepLooking = False Else Set FirstFound = FoundCell KeepLooking = True End If Do While KeepLooking Ct = Ct + 1 Cell2Find.Offset(, Ct).Hyperlinks.Add Cell2Find.Offset(, Ct), "", FoundCell.Address(external:=True), FoundCell.Address(external:=True) 'Set FoundCell = Nothing Set FoundCell = .UsedRange.FindNext(FoundCell) If FoundCell Is Nothing Then KeepLooking = False If FoundCell.Address = FirstFound.Address Then KeepLooking = False Loop End With End If Next End Sub
JKPieterse Attached is the copy of a sample data.
AbtinS I see my code only looks for cells which contain just the word you are looking for. This adjusted version looks for cells containing the words:
Option Explicit
Sub ListHits()
Dim Cell2Find As Range
Dim FoundCell As Range
Dim FirstFound As Range
Dim Ct As Long
Dim KeepLooking As Boolean
For Each Cell2Find In Worksheets("Keywords").UsedRange
If Len(Cell2Find.Value) > 0 Then
Set FirstFound = Nothing
With Worksheets("Data")
Ct = 0
Set FoundCell = Nothing
Set FoundCell = .UsedRange.Find(What:=Cell2Find.Value, After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If FoundCell Is Nothing Then
KeepLooking = False
Else
Set FirstFound = FoundCell
KeepLooking = True
End If
Do While KeepLooking
Ct = Ct + 1
Cell2Find.Offset(, Ct).Hyperlinks.Add Cell2Find.Offset(, Ct), "", FoundCell.Address(external:=True), FoundCell.Address(external:=True)
'Set FoundCell = Nothing
Set FoundCell = .UsedRange.FindNext(FoundCell)
If FoundCell Is Nothing Then KeepLooking = False
If FoundCell.Address = FirstFound.Address Then KeepLooking = False
Loop
End With
End If
Next
End Sub
- Hi AlexA007 Can you post an Excel file with some (anonymized) sample data in which you highlight the expected outcome?
I am trying to look for words on a cell that with an specific criteria to search for example, if cells A2 contains AR to check cell B2 for certain words on cell X2, X3, X4,... that match it, the problem that I have about 34 different conditions to analyse it, I have tried with an excel formula but it throws a lot of false positives ( =SUMPRODUCT(--ISNUMBER(SEARCH($X2$:$X34$,B2)))>0 ) because I packed all the certain words on one list. I can't think of a way to isolate my search for all the 34 different conditions in a way to avoid the false positives bear in mind this excel doc has about 15k rows. I wonder if I could adjust my formula to be a bit more specific, I have tried with IF statements but it didn't work either, i wonder if there is a way to create a macro / vba to do it Many thanks
JKPieterse How do I add that in Worksheet And This picture is an example of what I want
Hi JKPieterse
thanks , I have had a look into advanced filter and it could be useful but I kind of need to know the location of the row item which contains the word combination, rather than just have the word returned as it is part of a bigger data set. I have taken out the two columns of narrative text which I need searched from the main data set by using a pivot table where they are displayed in one column. I'm quite confident I can find the original line items again so currently the data I need searched is all in one column but across ~95000 rows. Can you help? thanks
elmajen How to do that depends on how your data is organised. If it is all in one column you may consider using advanced filter to do this.
JKPieterse this was reall useful, thanks , is there any way you can addapt the code to include 'AND'/ 'OR' searh functions.
I would like to perform a word search like this;
financial inclusion OR payments, OR savings, OR credit, OR lending, OR loans, microloans, OR microfinance, OR insurance, OR mobile money
AND
Women, OR gender, OR female, equality, OR cooperative, OR garment workers, OR microentrepreneur, empowerment, OR VSLA, OR marginalised
Thanks
E
JKPieterse Thanks. This seems to be working well. Great for an attorney conflicts of interest checker.
- Copy the code from my post, open the Excel file, Alt+F11 to go to the VBA editor. Find a module in the list of modules in the small tree on the left that contains the "old" code and replace all of it with the bit you copied.
JKPieterse How do I update the spreadsheet to include the new code?
