CODEWITHSUNDEEP
excelvbaexcel-formula
aoswald
aoswald

Reputation: 107

using a variable in an R1C1 formula

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

Upvotes: 1

Views: 11945

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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.

enter image description here

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.

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166351

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

Upvotes: 3

Related Questions