Solved search multiple words in excel

October 11, 2015 at 08:46:11
Specs: Windows 7
I need to search 10 words in 10 excel files at once. Can anyone give me a macro for doing this? For example i want to search london,doha,delhi in 10-15 files in a folder at once. Is this possible with a macro?

Please help.

Thanks in advance.


See More: search multiple words in excel

Report •


✔ Best Answer
October 13, 2015 at 13:24:13
Try this code.

I added a section to make sure that you don't select any blank cells. Since there are a gazillion blank cells in every workbook, the code will find them and run forever.

I also commented out the instructions that populate A1 and B1. Those instructions are expecting a single search string, which we no longer have.

Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim a As Single
Dim sht As Worksheet
Dim serRng As Range



Set WS = Sheets.Add

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    myfolder = .SelectedItems(1) & "\"
End With

'Get Search String Range From User
 On Error Resume Next
   Set serRng = Application.InputBox _
       (prompt:="Select or Enter Range With Search Strings" & vbCrLf & vbCrLf & _
                "Click OK When Done" & vbCrLf & vbCrLf & _
                "Click Cancel To Exit", _
                Title:="Search all workbooks in a folder", Type:=8)

 On Error GoTo 0
 
'Exit Sub If Cancel Clicked
 If serRng Is Nothing Then Exit Sub
 
'Do not allow blank cells in Search Range
 If WorksheetFunction.CountA(serRng) <> serRng.Cells.Count Then
  MsgBox "There Are Blank Cells In Your Range." & vbCrLf & vbCrLf & _
         "Blank Search Strings Are Not Allowed." & vbCrLf & vbCrLf & _
         "Please Try Again."
  Exit Sub
 End If

'Setup Worksheet Titles

'WS.Range("A1") = "Search string:" ***I'm not sure what you want here any more
'WS.Range("B1") = Str ***I'm not sure what you want here any more
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "String"
WS.Range("E3") = "Link"

'Search All Sheets In All Workbooks
'(More comments should be added to explain steps)

a = 0

Value = Dir(myfolder)
 Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If Right(Value, 3) = "xls" Or _
           Right(Value, 4) = "xlsx" Or _
           Right(Value, 4) = "xlsm" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
            If Err.Number > 0 Then
                WS.Range("A4").Offset(a, 0).Value = Value
                WS.Range("B4").Offset(a, 0).Value = "Password protected"
                a = a + 1
            Else
                On Error GoTo 0
 
'Search For Each String In Range
             For Each cell In serRng
               For Each sht In ActiveWorkbook.Worksheets
                        Set c = sht.Cells.Find(cell, LookIn:=xlValues, _
                                                     LookAt:=xlPart, _
                                                     SearchOrder:=xlByRows, _
                                                     SearchDirection:=xlNext)
                        If Not c Is Nothing Then
                            firstAddress = c.Address
                            Do
                              WS.Range("A4").Offset(a, 0).Value = Value
                              WS.Range("B4").Offset(a, 0).Value = sht.Name
                              WS.Range("C4").Offset(a, 0).Value = c.Address
                              WS.Range("D4").Offset(a, 0).Value = c.Value
                              WS.Hyperlinks.Add Anchor:=WS.Range("E4").Offset(a, 0), _
                                                Address:=myfolder & Value, _
                                                SubAddress:=sht.Name & "!" & _
                                                c.Address, TextToDisplay:="Link"
                                a = a + 1
                                Set c = sht.Cells.FindNext(c)
                            Loop While Not c Is Nothing And c.Address <> firstAddress
                        End If
                Next sht
             Next
           End If

             Workbooks(Value).Close False
            On Error GoTo 0
        End If
    End If
    Value = Dir
 Loop

Cells.EntireColumn.AutoFit
End Sub

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



#1
October 11, 2015 at 11:30:09
It's probably possible, but I'm sure there are lots of things you haven't told us. There's no sense in writing code until we have all of the information.

What do you want to happen after the word or words are found?
What do you want to happen if no words are found?
Should the code stop after the first word is found or does it have to search every file for every word?
Does the code have to search every cell in every sheet in every file or is the search area limited in some way?

As you can see, there is a number of things we need to know before we can offer any code that will fit your needs. Please give us all the details of what you are trying to do.

In addition, unless you know how to write/modify VBA code, please don't try to "keep it simple". What often happens is that we end up writing code to address the "simple" requirements, then we have to add code or modify code to meet additional requirements.

If you tell us everything we need to know right up front, it's easier on us.

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


Report •

#2
October 11, 2015 at 21:47:10
Presently I am suing this code for searching. But I can search only one word at a time. But I want to search 10 or more words at once and the result should be displayed in a sheet with link. I tried to modify the code but every time I faced a lot of errors. I am not an VBA expert and it is difficult for me to find out a solution myself. My requirement is : 1. I have to select the folder. All files in that folder has to be searched for the words given in a range. (like the below code)
2. If no result found a blank sheet with headings may be displayed. (like the below code)
3. Code should search for all words in all files.
4. All the results to be displayed in a single sheet.
-----------------------------------
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet

Set WS = Sheets.Add

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With

Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2)

If Str = "" Then Exit Sub

WS.Range("A1") = "Search string:"
WS.Range("B1") = Str
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"

a = 0

Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each sht In ActiveWorkbook.Worksheets
Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstAddress = c.Address
Do
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
sht.Name & "!" & c.Address, TextToDisplay:="Link"
a = a + 1
Set c = sht.Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next sht
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub
-----------------------------


Report •

#3
October 12, 2015 at 05:33:15
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then repost your code so that it looks similar to the example found via that link. Thanks!

Second, I'm a little curious as to why you didn't post the code that you already had and ask that it be modified to fit your new requirements. It sure would have been a waste of time for someone to have started from scratch when the most of the work has already been done.

In any case, please read the following instructions and then repost your code using the pre tags as described.

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


Report •

Related Solutions

#4
October 12, 2015 at 07:24:01
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet

Set WS = Sheets.Add

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    myfolder = .SelectedItems(1) & "\"
End With

Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2)

If Str = "" Then Exit Sub

WS.Range("A1") = "Search string:"
WS.Range("B1") = Str
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"

a = 0

Value = Dir(myfolder)
Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
            If Err.Number > 0 Then
                WS.Range("A4").Offset(a, 0).Value = Value
                WS.Range("B4").Offset(a, 0).Value = "Password protected"
                a = a + 1
            Else
                On Error GoTo 0
                For Each sht In ActiveWorkbook.Worksheets
                        Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
                        If Not c Is Nothing Then
                            firstAddress = c.Address
                            Do
                                WS.Range("A4").Offset(a, 0).Value = Value
                                WS.Range("B4").Offset(a, 0).Value = sht.Name
                                WS.Range("C4").Offset(a, 0).Value = c.Address
                                WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
                                sht.Name & "!" & c.Address, TextToDisplay:="Link"
                                a = a + 1
                                Set c = sht.Cells.FindNext(c)
                            Loop While Not c Is Nothing And c.Address <> firstAddress
                        End If
                Next sht
            End If
            Workbooks(Value).Close False
            On Error GoTo 0
        End If
    End If
    Value = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub


Report •

#5
October 12, 2015 at 07:30:46
Thank you very much for the guidance. First I did not post the code because I thought it is not necessary. I have reposted the code. If I get the code as per my requirement I can save 10 minutes in my office work. Please help. Thanks in advance.

Report •

#6
October 12, 2015 at 11:16:06
re: "I did not post the code because I thought it is not necessary."

Not to belabor the point, but in order for us to have answered the question you posed ("Can anyone give me a macro for doing this?") we would have had to start from scratch and write every step of the code. It would have been a lot of work to duplicate what you already had. Once I saw what you already had, it was a simple matter of adding a few more instructions and doing a little editing. That was a lot less work than starting with nothing and writing the entire macro from the beginning.

BTW...I noticed that you don't have any comments in the macro that you posted. Comments are very useful, not just when posting code for others to read, but also for the author. A wise man once said:

"Code tells you how, Comments tell you why."

Imagine coming back to this code a year or more from today and trying to remember exactly what each section does and why you did it that way. Getting into the habit of adding comments makes it so much easier to understand what the code is supposed to be doing, not only for the author, but also for those they may need to read and/or modify the code.

Finally, I don't know if you use any debugging techniques when your code presents errors, so I'd like to offer this tutorial:

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

These debugging techniques are useful for not only finding problems with your own code, but also for "reverse engineering" code that you find on the internet or in someone else's workbook.

OK, so back to your question...

What I did was use a 10 element Array "Str(10)" to accept up to 10 string inputs. The code loops through the "InputBox" section gathering the strings from the user and building the Array until the user clicks Cancel.

Once the Array has been built, the code loops through the array, searching for each array element. In other words, instead of searching for the single string "Str" like it did in the original code, it now searches for "Str(0)", then "Str(1)", then "Str(2)", etc.

I also added a column to the output sheet to display the string that was found in the same row as the other information that you were already displaying. e.g.

Workbook     Worksheet     Cell Address     String     Link


Let me know how it works for you...

Sub SearchWKBooksMultStrings()
Dim WS As Worksheet
Dim myfolder As String
Dim a As Single
Dim sht As Worksheet
Dim Str(10) As Variant '10 Element Array
Dim elmCnt As Integer  'Element Counter for Input
Dim elmNum As Integer  'Element Counter For Output


Set WS = Sheets.Add

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    myfolder = .SelectedItems(1) & "\"
End With

'Get First String From Users
   Str(elmCnt) = Application.InputBox _
                 (prompt:="Enter Search String." & vbCrLf & vbCrLf & _
                 "Click OK After Each Entry, Click Cancel When Done", _
                 Title:="Search all workbooks in a folder", Type:=2)

'Get Additional Strings, Stop If Cancel Chosen
  Do Until Str(elmCnt) = False
    elmCnt = elmCnt + 1
     Str(elmCnt) = Application.InputBox _
                   (prompt:="Enter Search String." & vbCrLf & vbCrLf & _
                   "Click OK After Each Entry, Click Cancel When Done", _
                   Title:="Search all workbooks in a folder", Type:=2)
  Loop

'Exit Sub If No String Entered
  If Str(0) = "" Then Exit Sub

'Setup Worksheet Titles

WS.Range("A1") = "Search string:"
WS.Range("B1") = Str
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "String"
WS.Range("E3") = "Link"

'Search All Sheets In All Workbooks
'(More comments should be added to explain steps)

a = 0

Value = Dir(myfolder)
 Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If Right(Value, 3) = "xls" Or _
           Right(Value, 4) = "xlsx" Or _
           Right(Value, 4) = "xlsm" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
            If Err.Number > 0 Then
                WS.Range("A4").Offset(a, 0).Value = Value
                WS.Range("B4").Offset(a, 0).Value = "Password protected"
                a = a + 1
            Else
                On Error GoTo 0
 
'Search For Each Element In Array
             For elmNum = 0 To elmCnt - 1
               For Each sht In ActiveWorkbook.Worksheets
                     Set c = sht.Cells.Find(Str(elmNum), _
                           LookIn:=xlValues, LookAt:=xlPart, _
                           SearchOrder:=xlByRows, SearchDirection:=xlNext)
                       If Not c Is Nothing Then
                         firstAddress = c.Address
                          Do
                           WS.Range("A4").Offset(a, 0).Value = Value
                           WS.Range("B4").Offset(a, 0).Value = sht.Name
                           WS.Range("C4").Offset(a, 0).Value = c.Address
                           WS.Range("D4").Offset(a, 0).Value = Str(elmNum)
                           WS.Hyperlinks.Add _
                               Anchor:=WS.Range("E4").Offset(a, 0), _
                               Address:=myfolder & Value, SubAddress:= _
                               sht.Name & "!" & c.Address, TextToDisplay:="Link"
                                a = a + 1
                                Set c = sht.Cells.FindNext(c)
                            Loop While Not c Is Nothing And c.Address <> firstAddress
                        End If
                Next sht
             Next
           End If

     Workbooks(Value).Close False
            On Error GoTo 0
        End If
    End If
    Value = Dir
 Loop

Cells.EntireColumn.AutoFit
End Sub

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


Report •

#7
October 12, 2015 at 19:24:38
Thank you very much for your valuable suggestions. I am just starting learning VB and I know I have to learn a lot. The code works perfectly as per my requirement. But I want like search. Even though LookAt:=xlPart , only the whole word search is happening. And another one - is it possible to select a range instead of typing words one by one?
Thank you once again for spending your valuable time for answering my queries.

Looking for your reply.


Report •

#8
October 12, 2015 at 20:00:25
xlPart means that Excel will return any cell that contains the search string, either by itself or as part of a longer string.

e.g. When using xlPart and searching for "car", Excel will return any cell containing:

The car is red.
I care about my kids.
He is a Madagascarian.

If that is not what you want, please provide a few examples of your search strings and the string in which you would expect them to be found.

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


Report •

#9
October 13, 2015 at 07:38:22
I want to search a cell containing my search string. for example if my search string is London, cell containing London-UK is also to be included in the search result. I thought that xlpart will do this. But in the code even though the LookAt:=xlPart the cell with exact match the string are searched. I cant understand the mistake in the code. Of course, i can handle this by adding * as prefix and suffix in my search string. But if the code will do that it is better.
Another one, selecting a range of search strings at once instead of selecting or typing one by one. For example if i have to search for 10 strings, i will put them in cell A1:A10 and in search string input box i have to select the range. Whether this code can be modified like this?
Waiting for your valuable reply.

Report •

#10
October 13, 2015 at 08:32:36
I am not having any problem with the xlPart code.

I created 2 workbooks and included the following strings in various cells in various sheets:

London-UK
London is nice
UK-London-UK
ppppLondonpppp
London
London Bridge
Bridge London Bridge

I used a search string of London and the code found every one of the above strings.

Perhaps you are confused by the output that my code is presenting? The extra column I added returns the search string, not the contents of the cell where the search string was found. I did this so that you can see which sheet contains which search string when using multiple search strings.

If you want to see the contents of the cell that was found, simply change this instruction:

 WS.Range("D4").Offset(a, 0).Value = Str(elmNum)

to be

WS.Range("D4").Offset(a, 0).Value = c.Value

If the code is not finding the cells that contain the search string when it is only part of the cell, then there may be something going on with your data, not with the code.

In the meantime, I will work on the "selection" question that you raised.

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


Report •

#11
October 13, 2015 at 09:05:49
Yes, Working perfectly. Thank you very much. Now I am worried with how to modify the code for selecting range as multiple search string.
I know it is not fair to ask everything. I have learned a lot from your guidance. If the code modified for selection of range , then this code become very useful to me. I am very grateful to you for sharing your knowledge.

Report •

#12
October 13, 2015 at 13:24:13
✔ Best Answer
Try this code.

I added a section to make sure that you don't select any blank cells. Since there are a gazillion blank cells in every workbook, the code will find them and run forever.

I also commented out the instructions that populate A1 and B1. Those instructions are expecting a single search string, which we no longer have.

Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim a As Single
Dim sht As Worksheet
Dim serRng As Range



Set WS = Sheets.Add

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    myfolder = .SelectedItems(1) & "\"
End With

'Get Search String Range From User
 On Error Resume Next
   Set serRng = Application.InputBox _
       (prompt:="Select or Enter Range With Search Strings" & vbCrLf & vbCrLf & _
                "Click OK When Done" & vbCrLf & vbCrLf & _
                "Click Cancel To Exit", _
                Title:="Search all workbooks in a folder", Type:=8)

 On Error GoTo 0
 
'Exit Sub If Cancel Clicked
 If serRng Is Nothing Then Exit Sub
 
'Do not allow blank cells in Search Range
 If WorksheetFunction.CountA(serRng) <> serRng.Cells.Count Then
  MsgBox "There Are Blank Cells In Your Range." & vbCrLf & vbCrLf & _
         "Blank Search Strings Are Not Allowed." & vbCrLf & vbCrLf & _
         "Please Try Again."
  Exit Sub
 End If

'Setup Worksheet Titles

'WS.Range("A1") = "Search string:" ***I'm not sure what you want here any more
'WS.Range("B1") = Str ***I'm not sure what you want here any more
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "String"
WS.Range("E3") = "Link"

'Search All Sheets In All Workbooks
'(More comments should be added to explain steps)

a = 0

Value = Dir(myfolder)
 Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If Right(Value, 3) = "xls" Or _
           Right(Value, 4) = "xlsx" Or _
           Right(Value, 4) = "xlsm" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
            If Err.Number > 0 Then
                WS.Range("A4").Offset(a, 0).Value = Value
                WS.Range("B4").Offset(a, 0).Value = "Password protected"
                a = a + 1
            Else
                On Error GoTo 0
 
'Search For Each String In Range
             For Each cell In serRng
               For Each sht In ActiveWorkbook.Worksheets
                        Set c = sht.Cells.Find(cell, LookIn:=xlValues, _
                                                     LookAt:=xlPart, _
                                                     SearchOrder:=xlByRows, _
                                                     SearchDirection:=xlNext)
                        If Not c Is Nothing Then
                            firstAddress = c.Address
                            Do
                              WS.Range("A4").Offset(a, 0).Value = Value
                              WS.Range("B4").Offset(a, 0).Value = sht.Name
                              WS.Range("C4").Offset(a, 0).Value = c.Address
                              WS.Range("D4").Offset(a, 0).Value = c.Value
                              WS.Hyperlinks.Add Anchor:=WS.Range("E4").Offset(a, 0), _
                                                Address:=myfolder & Value, _
                                                SubAddress:=sht.Name & "!" & _
                                                c.Address, TextToDisplay:="Link"
                                a = a + 1
                                Set c = sht.Cells.FindNext(c)
                            Loop While Not c Is Nothing And c.Address <> firstAddress
                        End If
                Next sht
             Next
           End If

             Workbooks(Value).Close False
            On Error GoTo 0
        End If
    End If
    Value = Dir
 Loop

Cells.EntireColumn.AutoFit
End Sub

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


Report •

#13
October 13, 2015 at 19:22:13
Superb! You are great! Working exactly as I wanted. Thanks a lot.

Report •

#14
November 19, 2015 at 19:22:34
I have a question about your formula - so I have a list of keywords I need to search for on my excel sheet. Example of my keywords are (white, red, black, green, yellow, blue) where on this formula would I change to include the colorls I wanted to search for?

If the cell contained part of the "red" like the word Reddick and Reddit, I would like to show that red had matches for Reddick and reddit.

If there's no search, having it say there's nothing found is fine


Report •

#15
November 20, 2015 at 05:44:33
HI! I've read your question but I won't be able to dig into it until sometime this weekend.

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


Report •

#16
November 23, 2015 at 11:19:03
Than you for the help!

Report •

#17
November 23, 2015 at 11:50:49
Don't thank me yet...I never got to it this weekend.

Life has a strange habit of happening when I least expect it to. ;-)

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


Report •

#18
December 2, 2015 at 07:43:41
Any luck? :)

Report •

Ask Question