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

ekaplan323
ekaplan323 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
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...

Author

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.
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
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Top Expert 2015

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

Saurabh...

Author

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.

Author

Commented:
Here is the file again.
find-Test-Data.xlsx
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
Top Expert 2015

Commented:
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

Author

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
Top Expert 2015
Commented:
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

Author

Commented:
Thanks very much, a life saver!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial