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

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

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

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