Microsoft Knowledge Base

• Microsoft Excel for Windows, versions 4.0, 4.0a
• Microsoft Excel for the Macintosh, version 4.0

SUMMARY

In Microsoft Excel versions 4.0 and later, when you run a recorded a macro that uses the FORMULA.REPLACE() macro function, you may receive the "Parentheses do not match" error message.

MORE INFORMATION

In Microsoft Excel versions 4.0 and later, if you forget to include the final closing parenthesis in a worksheet formula, Microsoft Excel automatically inserts one for you.

If you record a macro that uses the FORMULA.REPLACE() function to change a text string to a formula, you may receive a macro error when Microsoft Excel adds this missing closing parenthesis. For example, when you record a macro to change the text string "*=SUM($A$1:$A$2" to a formula by replacing "*=" with "=" (without the quotation marks), the formula is converted to =SUM($A$1:$A$2). When you run the recorded macro, you receive the "Parentheses do not match" error message.

Steps to Reproduce Problem

1. In cells A1:A5, enter some random numbers.

2. In cell C1, type:

         *=SUM($A$1:$A$2
   

3. Drag the fill handle to the right to fill the formula through to cell F1.

4. With the range C1:F1 selected, choose Record from the Macro menu. In the Name box, type "Replace" (without the quotation marks). Choose OK.

5. From the Formula menu, choose Replace.

6. In the Find What box, type "*=" (without the quotation marks) and in the Replace With box, type "=" (without the quotation marks). Choose the Replace All button.

1. From the Macro menu, choose Stop Recorder.

2. Repeat steps 2 and 3.

3. With the range C1:F1 selected, choose Run from the Macro menu, select the Replace macro from the Run list and choose OK.

REFERENCES

"Function Reference," version 4.0, page 174