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
