Forum Discussion

AbtinS's avatar
AbtinS
Copper Contributor
Apr 14, 2020
Solved

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? ...
  • JKPieterse's avatar
    JKPieterse
    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
    

Resources