Forum Discussion
AbtinS
Apr 14, 2020Copper Contributor
How do I find multiple search terms in Excel?
I often need to search a large list of search terms in an Excel sheet, but the search function is too tedious to do because I have to search for them individually. How do I search for these faster? ...
- 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
JKPieterse
Apr 15, 2020Silver Contributor
Can you perhaps upload a sample file with some (anonymous) data?
- AbtinSApr 16, 2020Copper Contributor
I tried to explain it again above if that helps. If not I can see if I can create a sample file.
- JKPieterseApr 16, 2020Silver Contributor
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?