XL: Find and Replace Commands Don't Work with Nonadjacent Cells

The information in this article applies to:

• Microsoft Excel for Windows, versions 4.0, 4.0a

SYMPTOMS

In Microsoft Excel versions 4.0 and 4.0a for Windows, when you use the Find and Replace commands (located on the Formula menu) on nonadjacent selections, you receive the following unexpected results:

• Microsoft Excel may not find ALL of the matching cells in the selected range.
• Microsoft Excel may find and replace the contents of cells that are not part of the selected ranges.

Note: These commands work correctly in Microsoft Excel versions 3.0 and 4.0 for the Macintosh and in Microsoft Excel version 3.0 for Windows.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. This problem was corrected in a later version of Microsoft Excel.

MORE INFORMATION

The incorrect selections will vary depending on which cell is active in the selection, the option that is selected under Look By in the Find and Replace dialog boxes, and the relationship of the Nonadjacent ranges.

WORKAROUND

To work around this problem, select an adjacent range of cells when you use the Find or Replace commands on the Formula menu.

The Replace command fails in the same way as the Find command does when you are using the Find Next and Replace buttons to individually change cells. If you use the Replace All button, all of the matching cells will be correctly found and replaced with the text specified in the Replace With box. The one instance in which Replace All may fail is when there is a matching cell between the nonadjacent selections and, prior to selecting Replace All, you toggled through each of the matching cells using Find Next.

Steps to Reproduce the Problem

Following are examples where the Find and Replace commands do not work correctly.

Example 1

1. Enter the following in cells A1:A5.

       A1: a
       A2: b
       A3: a
       A4: b
       A5: a
   

2. Select cells A1:A2. Then hold down the CTRL key while selecting cells A4:A5. Cell A4 will be the active cell.

3. From the Formula menu, choose Find. In the Find What box, Type "a" (without the quotation marks). Set the Look By option to Rows and choose OK.

   When you choose OK, cell A5 will be selected. Pressing F7 to find the next match will correctly choose A1. Pressing F7 once again will cause A1:A2 and A4:A5 to be highlighted with no active cell appearing to be chosen. Pressing F7 again will correctly select A5. Note that the same behavior occurs if you're using the Find Next button in the Formula Replace dialog box. If you then choose the Replace All button to change occurrences of a to c, the entries in cells A1, A3, and A5 will be changed to c. An a should remain in cell A3 since it wasn't part of the selected range.

Example 2

1. Enter the following into cells A1:C4.

        A1: a   B1: a   C1: b
        A2: a   B2: b   C2: b
        A3: b   B3:     C3: a
        A4: b   B4:     C4: a
   
   

2. Select cells A1:A4. Then hold down the CTRL key while selecting cells C1:C4. Cell C1 will be the active cell.

3. From the Formula menu, choose Find. In the Find What box, type "a" (without the quotation marks). Set the Look By option to Rows and choose OK.

If you begin with cell A1 active by selecting C1:C4 first and then A1:A4, choosing Find from the Formula menu and pressing F7 will take you from cell A1 to cell A2 to cell C3 where it stops. Pressing F7 continually selects cell C3 when it should go to cell C4 next and then back to cell A1.

Again with A1 the active cell in the Nonadjacent selection, if you select the Look By Columns option instead of Rows, the Find command and F7 correctly selects cells A1 and A2. Next C3 and C4 will be selected but pressing F7 should take you back to A1; C3 is selected instead.

Different behavior may occur when the one range is above or below another and this will vary according to the option you have selected for the Look By option.

REFERENCES

"User's Guide 1," pages 202-204.