XL: How to Search All Worksheets in a Workbook

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, you can search on an individual worksheet by clicking Find on the Edit menu. However, to search all of the worksheets in a workbook in a convenient manner requires that you use a Microsoft Visual Basic for Applications procedure. This article includes a Visual Basic macro (Sub procedure) that you can use to accomplish this task. As an alternative to using Visual Basic, you can group the worksheets before you start a search.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

Sample Visual Basic Procedure

   Sub FindData()

       ' This Sub prompts you for a value and searches each worksheet
       ' for the value. If it finds the search value, the routine 
       ' prompts you to continue the search on the sheet. If it does not
       ' find the value on the sheet, it goes to the next sheet. You can
       ' cancel the search on the sheet by answering No to "Look for
       ' another value : ?" and then answering Yes to "Cancel the
       ' search ...".

       testValue = InputBox("Enter the value to search for : ")
       For x = 1 To ActiveWorkbook.Worksheets.Count
           Worksheets(x).Select
           Set foundcell = ActiveSheet.Cells.Find(testValue)
           If foundcell Is Nothing Then
               MsgBox "The word was not found"
           Else
               MsgBox "The word was found in cell " & foundcell.Address
               Range(foundcell.Address).Select

       LookAgain:
               response = MsgBox _
                   ("Look for another value on this sheet?", vbYesNo)

               ' If response = 6, we will not continue searching on
               ' this sheet.
               If response = 6 Then

                   ' Part2
                   Set foundcell = _
                       ActiveSheet.Cells.FindNext(after:=ActiveCell)
                   Range(foundcell.Address).Select
                   GoTo LookAgain
               End If

               If response = 7 Then
                   response = MsgBox("Cancel search ? ", vbYesNo)
                   If response = 6 Then End
                   GoTo NextSheet
               End If
           End If

       NextSheet:
       Next x
       MsgBox "Search is complete ....."

   End Sub

REFERENCES

For more information about Looping, click the Index tab in Microsoft Excel 97 Visual Basic Help, type the following text

   For Each

"Visual Basic User's Guide," page 145-146