Help for search tab in excel sheet

[Keyur Patel]

I am looking for a method that I can search an entire workbook. I simply have several tabs (spreadsheets) and need to be able to search the entire workbook to find the spreadsheet that contains the information that I am looking for. Thanks in advance for any assistance.

 

[Somendra Misra]

Hi Keyur,

Welcome to the Chandoo forum.

This is how I do in Excel 2010.

1. Press Ctrl+F to open Find and replace dialogue box.
2. Enter the search criteria.
3. You can see there Option button. Press it, it will give you an option of WITHIN, select Workbook and press Find All.




You will get the result.

Regards,

 

[polarisking]

Keyur, this will display the worksheet name and cell address (in the Immediate Window) wherever the search value exists in the workbook.

Polarisking

Sub test()

    Dim ws      As Worksheet
    Dim c        As Range
 
    For Each ws In ActiveWorkbook.Worksheets
     
        For Each c In ws.UsedRange

            If InStr(c, "SEARCHVALUE") > 0 Then
                Debug.Print ws.Name & "|" & c.Address
            End If
        Next

    Next ws

End Sub