Article ID: 213740 - View products that this article applies to.
This article was previously published under Q213740
Unlike versions of Microsoft Excel earlier than Microsoft Excel 97, there is no menu command to record a macro that uses relative cell references in the versions of Excel listed at the beginning of this article. However, you can click Relative Reference on the Stop Recording toolbar.
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 a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:
Microsoft Certified Partners - https://partner.microsoft.com/global/30000104
Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
(https://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms)To record a macro that uses relative cell references, click Relative Reference on the Stop Recording toolbar. Clicking Relative Reference a second time turns off relative recording.
Note The ScreenTip for Relative Reference does not change to indicate whether relative recording is already available. The only indication that relative recording has started is the appearance of the Relative Reference button. When the button has a sunken or depressed three-dimensional appearance, relative recording has started.
If the Stop Recording toolbar does not appear when you begin recording a macro, you must display it. To display the Stop Recording toolbar, point to Toolbars on the View menu, and then click Stop Recording. When you do this, Microsoft Excel inserts an extra line of code in your macro. Note that when you run the recorded macro, the Stop Recording toolbar is displayed.
To avoid displaying the Stop Recording toolbar, follow these steps:
Absolute vs. Relative RecordingBy default, recorded macros use absolute cell referencing, which means that exact cell locations are recorded into the macro. The macro works only with the exact cells addresses you use when you record the macro.
When you record a macro with relative cell referencing, the actions recorded in the macro are relative to the starting cell location.
The following examples record the same action with absolute, and then relative, cell referencing.
Recording with Absolute Cell ReferencingTo record a macro with absolute cell referencing, follow these steps:
Recording with Relative Cell ReferencingTo record a macro with relative cell referencing, follow these steps:
For more information about recording macros, click Microsoft Excel Help on the Help menu, type record a macro in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Article ID: 213740 - Last Review: January 24, 2007 - Revision: 3.7