using a variable in an R1C1 formula

Question

This formula works: ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)" it returns =sum(c1:c14)

This formula does not work: ActiveCell.FormulaR1C1 = "=SUM(R[-" & nrows & "]C:R[-1]C)" It returns =SUM(C14:C1048576)

I have this statement

Dim nrows as Integer

why does the formula with the nrows variable not work

Answer 1

Your "wrong" results are because R1C1 formulas will "wrap", so for example if your active cell is A1 and you enter =R[-1]C you will get =A1048576 (last cell in ColA) as a result.

Similarly =RC[-1] in A1 gives you =XFD1

Answer 2

One should be careful while implementing R1C1 style formulas unless one is recording them directly using Macro Recorder.

Style is: <Row><Number><Column><Number>

First part is type of reference.

Absolute Reference: In case of absolute references, notation does not contain square brackets and number written indicates the explicit location of cell in the grid. e.g.

R1C1 denotes $A$1 i.e. Row 1 and Column 1

R2C1 denotes $A$2

R1C2 denotes $B$1

Usage of absolute references is fairly straightforward as one can fairly quickly understand the cell location in the grid.

Relative Reference: In case of relative references, notation requires usage of square brackets. See below image for clear understanding of offset notation.

So if your current cell is B2 then formula

=R[-1]C will refer B1

=RC[-1] will refer A2 and so on as shown in the image.

Your specific case:

You can use

ActiveCell.FormulaR1C1 = "=SUM(R1C:R" & (nrows - 1) & "C)"

which basically means if your ActiveCell is C16 and variable nrows is 16 then you will get following formula which uses absolute reference technique for rows.

=SUM(C$1:C$15) i.e. mixed references.

For relative references you can use:

ActiveCell.FormulaR1C1 = "=SUM(R[" & -(nrows - 1) & "]C:R[-1]C)" will get

=SUM(C1:C15) i.e. Relative References

For absolute references assuming you want in Column C it will become:

ActiveCell.FormulaR1C1 = "=SUM(R1C3:R" & (nrows - 1) & "C3)" will get

=SUM($C$1:$C$15) i.e. Absolute References

Additional notes:

• Notation =RC refers to ActiveCell itself.

• Relative style references are wrapped i.e. =RC[-1] in column A refers to the last column in the sheet and similarly =R[-1]C in the first row refers to the last row in the sheet.