XL98: Find Command Doesn't Work Across Worksheets in Group

• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

When you use the Find dialog box to locate specific information, Microsoft Excel finds only the first occurrence of the value (even if you have selected multiple worksheets that contain this information).

If the information is not located on the active worksheet, even if it is located on another sheet in the group, you receive the following message:

Cannot find matching data

CAUSE

This problem occurs because the Find command does not work across grouped worksheets. Microsoft Excel 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.

WORKAROUND

To work around this problem, you can use a Microsoft Visual Basic for Applications macro (Sub procedure) to search each worksheet that is selected in group mode.

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 professionals 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/overview/overview.asp

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 to start the 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. Click "Close and Return to Microsoft Excel" on the File menu to switch to Microsoft Excel.



6. Select the worksheets (group mode) that you want to search. Use the appropriate steps 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 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.

REFERENCES

For more information about the Find command, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Excel Help, type the following text

finding data

Additional query words: XL98

Keywords : kbdta kbdtacode xlui xlvbahowto
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbprb