Excel - finding text strings in a workbook on multiple worksheets.

I have a spreadsheet and  I want to create a list of key words to search for in a worksheet.  I want to search for these key words on other worksheets in the workbook (mostly included in a cell.)  Report back the number of "hits" and the location of the found string.
ekaplan323Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you post your sample file??

Also when you say make a list of keywords..You want to make it in a spreadsheet or you want to make it through inputbox??

In additional when you say report back the location..what you are looking for..sheet name along with cell address or something else??

Saurabh...
ekaplan323Author Commented:
Yes I think that might be fine.  I have attached some simple sample data.  The text on the key words worksheet is what you would be looking for contained in any of the other worksheets.
Excel amusantCommented:
Do you want something like this see attached.

Click on macro button an input box appears enter your searched string. If value found it highlights in red and also a message popups "Value found in sheet x".
Search-String-in-workbook.xlsm
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Saurabh Singh TeotiaCommented:
Sample Data is missing..Can you attach again..If your are keeping the word long..Please keep it short..

Saurabh...
ekaplan323Author Commented:
The data is in the workbook under the two Tabs "Business Disruption"  and "Clients, Prod, Bus Pract"

I shortened the data in the cells.
ekaplan323Author Commented:
Here is the file again.
find-Test-Data.xlsx
Excel amusantCommented:
Here is the code.
Sub macro1()
    Dim ws As Worksheet
    Dim rCl As Range
    Dim res As Range
    Dim lrow As Long
    
    
    Set res = Application.InputBox( _
    prompt:="Enter ID Number", Type:=8)
    If res = "" Then Exit Sub
    lrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row + 1
    For Each ws In Worksheets
        With ws.UsedRange
        
            Set rCl = .Find(What:=CStr(res), LookIn:=xlFormulas, LookAt:=xlWhole)
             If Not rCl Is Nothing Then
                Application.GoTo rCl, True
         
    rCl.Interior.ColorIndex = 3
ActiveWindow.LargeScroll ToRight:=-1

MsgBox "ID found " & ws.Name



            End If
        End With
    Next ws
    
    
    If rCl Is Nothing Then

 MsgBox "ID Not found"

End If

End Sub

Open in new window


Please see attached example.
find-Test-Data.xlsb
Saurabh Singh TeotiaCommented:
Their you go i believe this is what you are looking for..It will list down the Count of the total matches found in Column-B And in Subsequent cells it will list down the cell reference along with sheet names where it has found those matches..

I have attached your file post running the macro...You can run it by pressing alt+f8

Saurabh...
find-Test-Data.xlsm
ekaplan323Author Commented:
Saurabh,

What would I have to change if I only wanted to search one column on the worksheets like column c or column k for instance.

Thanks, Eric
Saurabh Singh TeotiaCommented:
Hi Eric,

Enclosed is the file for your reference where i changed to look at Column C and K only..

Saurabh...
find-Test-Data.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ekaplan323Author Commented:
Thanks very much, a life saver!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.