Article ID: 110709
Article Last Modified on 10/10/2006
APPLIES TO
- Microsoft Excel 5.0 Standard Edition
This article was previously published under Q110709
SUMMARY
In Microsoft Excel version 5.0 for Windows, when you are recording a Visual Basic subroutine and you drag a cell or range of cells to another cell or range of cells to replace the original information, the recorded subroutine may contain syntax errors that prevent it from functioning correctly.
WORKAROUND
To work around this problem, you may need to modify the recorded code. The modified code should resemble the following:
Sub Macro1() Range("A1").Select Selection.Cut (Range("B1")) Range("B1").Select End Sub
The third line, which originally contained invalid code, has been replaced by a line of code that performs the correct action.
MORE INFORMATION
Steps to Reproduce Problem
In a new workbook, create a new worksheet and enter the following values:
A1: Alpha B1: Bravo
- From the Tools menu, choose Record Macro, and then choose Record New Macro.
- In the Record New Macro dialog box, choose the Options button. In the Expanded Record New Macro dialog box, Under Language, verify that the Visual Basic option is selected. Choose OK to begin recording.
- Select the contents of cell A1 and drag it to cell B1.
- When you are prompted with the "Replace contents of destination cells?" message, choose OK.
- To stop the Macro Recorder, choose Record Macro from the Tools menu, and then choose Stop Recording.
- Switch to the new Visual Basic module.
The following subroutine should be contained in the module:
Sub Macro1() Range("A1").Select ExecuteExcel4Macro "CUT(Range("A1"),Range("B1"))" Range("B1").Select End Sub
The third line (which starts with the ExecuteExcel4Macro method) will appear in red, indicating that the line contains a syntax error.
- Switch to the worksheet.
- In cell A1, type Charlie.
- With cell A1 selected, choose Macro from the Tools menu.
- From the list of macros, select Macro1 and choose the Run button.
You will receive the error message "Syntax error" and the third line of the subroutine will be selected.
Additional query words: XL5
Keywords: kbprogramming KB110709