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
wumolad
Apr 15, 2020Iron Contributor
AbtinS do you mean there is a list of words to search for in a particular column? if so, you can look at this video. https://www.youtube.com/watch?v=D7vj7J7crB0
if this is not the situation, you can share some sample data to understand your question better.
AbtinS
Apr 16, 2020Copper Contributor
That video didn't apply to what I need. Basically I have a sheet with several columns, and many rows. Most of what I need to search for are names that are in 2 or 3 particular columns, but would preferably like to search the entire sheet. In addition, I have many search terms in a list, let's say a list in a word doc, in a format like this:
John Doe
Coke
Apple
etc.
I'm looking for a way to copy and paste that list into Excel and find out if I got a hit on any of the words, and if so where the hit is.
John Doe
Coke
Apple
etc.
I'm looking for a way to copy and paste that list into Excel and find out if I got a hit on any of the words, and if so where the hit is.