Find command doesn't work across Excel for Mac worksheets in group

This article was previously published under Q189629
This article has been archived. It is offered "as is" and will no longer be updated.
When you use the Find dialog box to locate specific information, Microsoft Excel for Mac only finds occurrences of the value on the active worksheet (even if you have selected multiple worksheets that contain this information).

If the information is not on the active worksheet, but is located on another sheet in the group, Excel for Mac may find the occurrence on the last sheet in the group with that value; however, it will not search any grouped sheets in between.

NOTE: This is not a problem when you use the Replace command. Only the Find command exhibits this problem.
This problem occurs because the Find command does not work across grouped worksheets. Excel for Mac Help states that if a group of sheets is selected, the Find command searches all of the sheets in the group except Visual Basic modules. This is not the actual behavior. When you select multiple worksheets, and you use the Find command to search for information on a worksheet, only the first occurrence of the value is found.
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 work around this problem, you can use a Microsoft Visual Basic forApplications macro (Sub procedure) to search each worksheet that isselected in group mode. To create the macro, follow these steps:
  1. Save and close any open workbooks, and then create a new workbook.
  2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. 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					
  5. On the File menu (or Excel menu in Microsoft Excel X for Mac), click Close and Return to Microsoft Excel.
  6. 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.

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

    • If you want to select all sheets in the workbook, hold down CONTROL, and click the sheet tab, and then click Select All Sheets on the shortcut menu.
  7. On the Tools menu, point to Macro, and then click Macros.
  8. Click the FindData macro, and then click Run. Follow the directions on the dialog boxes that appear.
For more information about the Find command, click Microsoft Excel for Mac Help on the Help menu, type find or replace data in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
MacXLX Mac XLX XL2001 XL98

Article ID: 189629 - Last Review: 12/05/2015 08:58:00 - Revision: 4.0

Microsoft Excel 2004 for Mac, Microsoft Excel X for Mac, Microsoft Excel 2001 for Mac, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kbdtacode kbprb KB189629