Excel - Rows and Columns RC - 7

December 2016



Issue


I am trying to do some extensive formula writing that involves complex sheet referencing. In my attempts, I do not understand the numbers that come after the R and C in the following. They do not seem to be linear like row and column headings. Could someone explain to me how these numbers work?


ActiveCell.FormulaR1C1 = "=SAF0006!R[-1]C[3]" 
Range("C3").Select 
ActiveCell.FormulaR1C1 = "=SAF0006!R[-2]C[7]"

Solution


The square brackets are used for relative addressing :

If the active cell is C3 (third row / third column), R[-2]C[7] corresponds to J1 (2 rows backwards and 7 columns forward)

For absolute addressing, do not use the square brackets :
R2C7 corresponds to $G$2 (second row / seventh column)

Note


Thanks to Ivan-hoe for this tip on the forum.

Related :

This document entitled « Excel - Rows and Columns RC - 7 » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.