Home/ Questions/Q 15074
Next
Answered
computing
  • 13
Asked: 2021-12-17T15:51:53+00:00 In:

Solved Create a Multiple Wordsearch And Filter Function In Excel

  • 13

I would like an option where users can enter various different word into a range (say 15 cells, though they may only use only one or a few of them) These words will then be search for in 2 columns of text. I would also like that the “raw” data would be filtered when the search is completed to only show content that had the searched words.

I have found conditional formatting that allows me to search for one word, and a marco to filter on the searched content. However I can not seem to find a way to search for multiple words/phrases at the same time. Again bearing in mind that 1 user may only have 1 word to search for, another may have 5 and another 10.

I am using Excel 2010

message edited by Elderine

Share

    1 Answer

    1. Best Answer
      Try the following code. You will need a sheet named Output for the well, umm, output. 😉

      I made a few assumptions:

      – Your Output sheet has headings in Row 1
      – You want the Output sheet cleared (except for the Heading Row) each time the Search button is used.
      – The Search strings are “top loaded” in Q25:Q39. (The code stops searching when it finds an empty cell in that range)
      – None of the search strings will be found in the Heading Row(s) for Raw!D:E (As written, the code searches the entire columns. That is easily modified.)
      – There may be more than one occurrence of a search string in Raw!D:E so the code checks for multiple occurrences and copies the data for each one found.

      BTW…I don’t know your level of VBA expertise, so may I suggest that you review the Tutorial found here?

      http://www.computing.net/howtos/sho…

      The debugging techniques discussed can not only help you write and fix VBA code, but they are a great way to reverse-engineer code that you find in forums such as these so that you can modify it for your own use.

      Let me know what you think.

      Option Explicit
Sub MultiSearch()
Dim cell As Range, c As Range
Dim firstAddress As String
Dim nxtRw As Long
'Clear Output Sheet except for Row 1 Headings
     Sheets("Output").Range("A2:CK" & Rows.Count).ClearContents
'Loop through Analysis!Q25:Q39
    For Each cell In Sheets("Analysis").Range("Q25:Q39")
'Exit if cell is Empty (less than 15 Search Strings)
     If cell = "" Then Exit Sub
'Search Raw!D:E
     With Sheets("Raw").Range("D:E")
      Set c = .Find(cell, lookat:=xlPart)
        If Not c Is Nothing Then
          firstAddress = c.Address
'Determine next empty Row in Output, Copy/Paste data
'Search for multiple occurances of current search string
         Do
           nxtRw = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row + 1
              Sheets("Raw").Range("A" & c.Row & ":CK" & c.Row).Copy _
                Sheets("Output").Range("A" & nxtRw)
              Set c = .FindNext(c)
         Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
     End With
    Next
End Sub

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0