Microsoft KB Archive/51302

{| = Excel: Cell Reference Formats for an Excel Macro =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q51302

SUMMARY
Before examining the different cell reference forms, it should be noted that Microsoft Excel can operate with either A1 or R1C1 type of cell references. The first part of this discussion focuses on the A1 type reference, followed by descriptions of the R1C1 type reference.

$A$1
This is an absolute reference to a cell on the sheet that contains this statement. For example, the following reference selects cell B3 on the macro sheet that contains the SELECT statement:

=SELECT($B$3)

A1
This is a relative reference to a cell on the sheet that contains this statement. For example, the following reference selects the cell B3 on the macro sheet that contains the statement, but maintains B3 as a relative reference on the sheet where it appears [i.e., if the =SELECT(B3) statement is in cell A1 and is copied to cell A2, Excel changes the relative reference so that cell A2 contains =SELECT(B4)]:

=SELECT(B3)

!$A$1
This is an absolute reference to a cell on the active sheet. For example, the following reference selects cell B2 on the worksheet named &quot;example&quot;:

=ACTIVATE(&quot;example&quot;) =SELECT(!$B$2)

!A1
This is a relative reference to a cell on the active sheet. For example, the following reference selects cell B2 on worksheet &quot;example&quot; and maintains B2 as a relative reference on the sheet where it appears (see also &quot;A1&quot;, above):

=ACTIVATE(&quot;example&quot;) =SELECT(!B2)

'Worksheet Name'!A1 This is a relative reference to a cell on the specified worksheet.


 * IMPORTANT: The following information assumes that the sheet is set to R1C1 style references. ******************************************************************

R1C1
This is an absolute reference to a cell on the sheet that contains the statement. For example, the following reference selects cell R3C2 (same cell as B3 when using the A1 type of reference) on the macro sheet that contains the SELECT statement:

=SELECT(R3C2) R[#]C[#] (where &quot;#&quot; is a positive or negative integer) This is a relative reference to a cell on the sheet that contains this statement. For example, the following reference selects the cell (on the macro sheet that contains the statement) that is three rows down and two columns to the right of the cell that contains the SELECT statement:

=SELECT(R[3]C[2])

!R1C1
This is an absolute reference to a cell on the active sheet. For example, the following reference selects cell R2C2 on the worksheet named &quot;example&quot;:

=ACTIVATE(&quot;example&quot;) =SELECT(!R2C2) !R[#]C[#] (where # is a positive or negative integer) This is a relative reference to a cell on the active sheet, but its relativity is to the cell that contains the macro statement. For example, the following reference selects the cell (on the active worksheet) that is three rows down and two columns to the right of the cell on the macro sheet that contains the SELECT statement:

=ACTIVATE(&quot;Worksheet1&quot;) =SELECT(!R[3]C[2]) In the following example, the cell at row 7, column 4 is selected as a result of running the macro on Macro1, which has the SELECT statement in row 4, column 2: =ACTIVATE(&quot;SalesQ1&quot;) =SELECT(!R[3]C[2]) =RETURN Note: This reference type can produce confusing results. Be sure you understand its operation before using it in a macro. 'Worksheet Name'!R1C1 This is an absolute reference to a specific cell on a named sheet.

'Example Sheet'!R[#]C[#] (where # is a positive or negative integer) This is a reference to a cell on the sheet named &quot;Example Sheet&quot; whose position is determined relative to the cell on the macro sheet in which the statement appears.

For additional information on references not parsed until execution, query on the following words:

parsed and execution
 * }