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

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

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

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

Saurabh...

The data is in the workbook under the two Tabs "Business Disruption"  and "Clients, Prod, Bus Pract"

I shortened the data in the cells.

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

Select allOpen in new window

Please see attached example.
find-Test-Data.xlsb

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

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

Thanks very much, a life saver!!