Excel: Using Relative R1C1 References in Macros

ID: Q46514

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0

SUMMARY

When you write a Microsoft Excel macro in R1C1 mode, relative references to the worksheet refer to cells on the worksheet relative to the cell containing the reference on the macro sheet. This behavior is often encountered when using the FORMULA command function, which places the formula text in the cell on the indicated worksheet, offset from the cell containing the FORMULA command on the macro sheet. The following is an example:


   R10C1   =FORMULA("text",Worksheet1!R[3]C)

The above statement in R10C1 places "text" in cell R13C1 of Worksheet1, regardless of which cell is currently selected in Worksheet1. R13C1 is three rows and no columns (R[3]C) offset from R10C1 in the macro sheet.

This information is true for any relative references while in R1C1 notation and should be kept in mind whenever you write macros that reference other worksheets. The only time a reference will be relative to the worksheet is if it is an R1C1 style reference passed as text, such as SELECT("R[3]C1") or FORMULA("=SUM(RC:R[10]C)").

MORE INFORMATION

This feature is by design and is consistent with A1 style references. When a relative reference is placed in a cell, that reference is always relative to the cell containing the formula. In R1C1 notation, an expression of a reference such as R[X]C is treated as an offset from the active cell's row number by X rows. Therefore, the formula used with this notation in a macro sheet modifies the cell in the worksheet that is an offset from the cell containing the formula in the macro sheet. This is true when you use a column offset, such as RC[X], as well, where the reference is offset by X columns.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 167-169

"Microsoft Excel Function Reference," version 3.0, pages 89-90

"Microsoft Excel Functions and Macros," version 2.2, pages 224-225

Additional query words: macrosheet

Keywords :
Version :
Platform :
Issue type :