Search across multiple sheets

**Scudder93** · 06-03-2011, 08:28 AM

Hi everyone, I've only recently started using more advanced excel tools and I have come across a problem.

The workbook contains lists of tools and their locations within a workshop. I am trying to create a search box which allows a part number to be searched for and the location is shown. I've got it working so that if the data in a cell is text or number then it works. But the problem comes because the book has several sheets which are all compiled into one (where the search function needs to be), and the part numbers are referenced to other sheets, for example, '='F Left'!C23'
This is causing the search box to come up with no results, and I was wondering if there was a way to search across multiple sheets?

Thanks in advance,

Chris.

**Mordred** · 06-03-2011, 09:38 AM

I don't know if this will work for you but try:

Sub SearchAcrossSheets()
    Dim ShtCnt As Long, ShtNum As Long
    Dim SearchStr As String
    Dim Found As Range
    ShtCnt = ActiveWorkbook.Sheets.Count
    SearchStr = InputBox("Please enter search criteria", "Search")
    For ShtNum = 1 To ShtCnt
        With Sheets(ShtNum)
            Set Found = .Cells.Find(SearchStr)
            If Not Found Is Nothing Then
                '''Do what you want with it here'''
            End If
        End With
    Next ShtNum
End Sub