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
Optin44
Mar 10, 2022Copper Contributor
AbtinS If i may jump-on this past chain and ask anyone about a simpler question... I have large national databases of clients, and we need to identify a handful of zip codes, and again, entering each one by one to search is ridiculous.
I'm not an excel guru, and have searched the basics on line, however no success trying figure this out.
This seems like it would be an easy formula, as example, enter 10 zip codes at once > then searches the sheet?
Please, I welcome any advise or assistance, Thank You!