Excel: Using R1C1 References as Text in a SELECT() Statement

Last reviewed: April 3, 1997
Article ID: Q51553

1.50 2.20 3.00 MACINTOSH macrosheet

SUMMARY

The following information details how to use R1C1 references as text in the SELECT() statement:

R1C1 Style References

Cell references passed as arguments to the SELECT() statement can be parsed to actual references at either macro creation time or macro run time. To hold a cell reference in its unparsed state, it must be surrounded by double quotation marks. This type of reference can be used when the macro sheet is in either A1 or R1C1 notation. For an example of R1C1 as a cell reference, query on the following words:

   R1C1 and reference and relative

"R1C1"

This is an absolute reference to the active cell on the active worksheet. Using the pair of quotation marks ("") in place of the exclamation mark (!) prevents this reference from being parsed until macro run time. This type of cell reference operates in the same manner as !R1C1 does as a cell reference.

"R[#]C[#]"

This is a relative reference to a cell on the active sheet; it is not similar to the !R[#]C[#] reference type, which is described in a separate article that can be found by querying on the following words:

   R1C1 and reference and relative

Using the pair of double quotation marks ("") in place of the exclamation mark (!) prevents this reference from being parsed until macro run time. The following macro will select the cell at row 3, column 3 on the sheet named "Example" (without quotation marks):

   =ACTIVATE("example")
   =SELECT("R1C1")
   =SELECT("R[2]C[2]")
   =RETURN()

The above form is usually appropriate to use when specifying a cell relative to the active cell.

MORE INFORMATION

This information was taken from the "Macro Tips" chapter in the "Microsoft Excel Technical Reference for the Macintosh" (pages 148-149). Please note that for most applications, using the OFFSET() statement will provide greater flexibility as well as increased speed, since parsing a text-style reference to an actual reference takes additional time each time the statement is encountered. Also note that the SELECT() statement is one of very few functions that will accept a text-style R1C1 reference. For more information on OFFSET(), query on the following keywords:

   offset and relative and reference

The "Microsoft Excel Technical Reference for the Macintosh" is part of the Excel Developer's Kit. This kit can be obtained by calling Microsoft End User Sales and Service at (800) 426-9400.

KBCategory: kbother
KBSubcategory:
Additional reference words: 1.50 2.20 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.