Microsoft KB Archive/146865

{| = XL: How to Search All Worksheets in a Workbook =
 * width="100%"|

Last reviewed: February 3, 1998

Article ID: Q146865 The information in this article applies to:
 * 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