Excel.Range R1C1 cell reference notation

 

Using R1C1 cell reference notation (R1C1) is preferred in Excel VBA rather than the more often seen A1 cell reference notation ($A$1).  The R stands for the row coordinate while the C stands for the column coordinate -  e.g. R2C2 is equivalent  B$2$.  Here are some rules for using the R1C1 cell reference style:

• For rows, positive values refer to cells towards the bottom of the spreadsheet whereas negative values refer to cells towards the top of the spreadsheet
• For columns, positive values refer to cells towards the right side of a spreadsheet and negative values refer to cells towards the left side of a spreadsheet.
• If you leave out a value for either the R or C coordinate,  than it means that you are referring  to the same row or column associated with the current cell location
• The first row or first column is 1

How to Use It

A cell reference can point to a single cell range, a bunch of cells or even entire row or column.   How?

Ex.	Address	A1 notation	R1C1 notation	Note
1		A$2$	=R2C1	Absolute address
2	$F$19	=MAX(C4:C17)	=MAX(R[-15]C[-3]:R[-2]C[-3])	Relative  addresses in formula
3	$F$20	=MIN(C:C)	=MIN(C[-3])	The entire column using relative address
4	$C$4	=ROUND(B4,0)	=ROUND(RC[-1],0)	Relative reference in formula

Tip:

Using CTRL + ` (French accent grave symbol)  keys to toggle from seeing the values (Normal view) to seeing formulas (Formula view) in the active worksheet