Forum Discussion
How do I find multiple search terms in Excel?
- Apr 27, 2020
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
I tried to explain it again above if that helps. If not I can see if I can create a sample file.
AbtinS Attached file with macro seems to do the trick.
- AbtinSApr 24, 2020Copper Contributor
JKPieterse I'm not sure how to use it. Do you have instructions?
- JKPieterseApr 24, 2020Silver ContributorSimple: Paste your list into column A of the Keywords sheet. Then press alt+F8 to get the list of macro's, click on "ListHits" and click Run.
- AbtinSApr 25, 2020Copper Contributor
JKPieterse This function wasn't available on Excel for Android version I was using. But I was able to get it to sort of work on Excel 2010. It worked fine in the sample data as you provided. But then I pasted my own date in the Data tab, and some test keywords in the Keywords tab, but then it kept showing hits to blank cells in the Data tab. Only correct hit I got was when the data cell and keyword were an exact match. I need it to pick out words that are parts of sentences in a Data cell. Also, I don't know why it keeps showing blank cells in Data page for keywords that are not blank.