Find Command Doesn't Work Across Worksheets in Group

This article was previously published under Q124105
This article has been archived. It is offered "as is" and will no longer be updated.
Symptoms
When you use the Find dialog box to locate specific information, MicrosoftExcel finds only the first occurrence of the value (even if you haveselected multiple worksheets that contain this information).

If the information is not located on the active worksheet, even if it islocated on another sheet in the group, you receive the following message:
Cannot find matching data
NOTE: This is not a problem when you use the Replace command. Only theFind command exhibits this problem.
Cause
This problem occurs because the Find command does not work across groupedworksheets. Microsoft Excel Help states that if a group of sheets isselected, the Find command searches all of the sheets in the group exceptVisual Basic modules. This is not the actual behavior. When you selectmultiple worksheets, and you use the Find command to search forinformation on a worksheet, only the first occurrence of the value isfound.
Status
Microsoft has confirmed this to be a problem in the Microsoft productslisted at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.
Workaround
To work around this problem, you can use a Microsoft Visual Basic forApplications macro (Sub procedure) to search each worksheet that isselected in group mode.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. To create the macro, follow these steps:
  1. Save and close any open workbooks, and then create a new workbook.
  2. If you are using Microsoft Excel 97, follow these steps:

    1. On the Tools menu, point to Macro, and then click Visual Basic Editor to start the Visual Basic Editor.
    2. On the Insert menu, click Module
    If you are using Microsoft Excel 7.0 or earlier, point to Macro on the Insert menu, and click Module.
  3. Type the following code in the module:
          Sub FindData()     ' This Sub prompts you for a value and searches each worksheet     ' that is selected in group mode. 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 ...".     '     ' WARNING: When this macro ends, the workbook will no longer be     ' in group mode.     testValue = InputBox("Enter the value to search for : ")     For Each x In ActiveWindow.SelectedSheets         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						
  4. If you are using Microsoft Excel 97, click "Close and Return to Microsoft Excel" on the File menu to switch to Microsoft Excel.
  5. Select the worksheets (group mode) that you want to search. Use the appropriate method for your situation:

    • If you want to select two or more adjacent sheets, click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet. -or-

    • If you want to select two or more nonadjacent sheets, click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets. -or-

    • If you want to select all sheets in the workbook, right-click a sheet tab, and then click Select All Sheets on the shortcut menu.
  6. If you are using Microsoft Excel 97, follow these steps:

    • On the Tools menu, point to Macro, and then click Macros.
    • Click the FindData macro, and then click Run. Follow the directions on the dialog boxes that appear.
    If you are using Microsoft Excel 7.0 or earlier, follow these steps:

    • On the Tools menu, click Macro.
    • Click FindData and click Run.
References

Microsoft Excel 97

For more information about the Find command on the Edit menu, click theIndex tab in Microsoft Excel Help, type the following text
Finding, data
and then double-click the selected text to go to the "Find or replacedata" topic.

Microsoft Excel 5.0

For more information about the Find Command (on the Edit Menu), click theSearch button in Help and type:
Find Command (Edit Menu)
5.00c 7.00a 97 XL97 XL7 XL5 XL
Properties

Article ID: 124105 - Last Review: 10/26/2013 12:18:00 - Revision: 3.0

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 for Macintosh
  • kbnosurvey kbarchive kbdtacode kbprb kbualink97 KB124105
Feedback