R1C1 notation uses the R value to show the row offset from the active cell and the C value
to show the column offset from the active cell. The offset value is enclosed in brackets; it can
be a negative number to show rows or columns with a lesser value than the active cell row or
column, or a positive number to show rows or columns with a greater value than the active
cell. If the reference is to the same row or column as the active cell, there is no value entered??”
only the letter R or C.
In the preceding example, the first call to the SUM function refers to the range
R[??“4]C:R[??“1]C. This is interpreted as a range starting four rows above the active cell (C6) in the
same column and ending in the cell one row above the active cell in the same column.
CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 19
You may be used to seeing the SUM function used with direct cell references like
=SUM(A1:A4), especially if you??™re entering formulas directly on a worksheet. If you are adding
a total to cell A5, this is a direct way to get the total of that range. But what if you need to add
a total value for a number of columns across a row under your data range through VBA code?
Using R1C1 notation, the formula =SUM(R[-4]C:R[-1]C) will always refer to rows 1 through 4
in the same column as the active cell (where the active cell is located in row 5).
As you??™ve seen, the VBE is where Excel??™s Macro Recorder stores the code it creates, and it??™s
where you will create and save the code you use in your daily tasks as well as in this book??™s
examples.
Pages:
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46