Excel VBA FormulaR1C1 Property of Range

What is FormulaR1C1 Property in Excel VBA?

In R1C1, “R” refers to row, while “C” refers to column. Formula R1C1 allows you set or retrieve formula for a cell, using relative row and column.

Why do you need FormulaR1C1?

For example, you want to set a formula in C2 to sum A2 and B2, you can type

Range("C2").formula= "=A2+B2" or
Range("C2").Value = "=A2+B2"

Instead of hard code “A2+B2”, you can also tell Excel to sum the two cells on the left of the formula cell using FormulaR1C1.

“A2+B2” works well until you insert a new column before A column, where the old A column becomes B, old B column becomes C ! Since you already hard code C2 = A2+B2, the formula will not change and the formula is messed up.

However, if your formula Cell C2 is dynamic, such as Named Range, after inserting a column the reference Range will become D2, and you can tell Excel to sum the two cells on the left, thats why FormulaR1C1 is useful.

Syntax

To retrieve a formula from a Range

variableName = Range.FormulaR1C1

To set a formula for a Range

Range.FormulaR1C1 = "=R[x]C[y]" or
Range.Value = "=R[x]C[y]"

x and y are the coordinate relative to the formula Cell

where x is the number of rows right to formula Cell  (negative value means left to the formula Cell)

where y is the column down from the formula Cell (negative value means up from the formula Cell)

You can omit the square brackets for x and y if the values are positive, which will cause the Cell to turn into absolute values with $

Note that when you set a formula, the result of Range.FormulaR1c1 and Range.Value are the same, the only difference is when you retrieve the value from the Range

VBA code Return
Msgbox(Range(“C2”).value) numeric value of A2+B2
Msgbox(Range(“C2”).formulaR1C1) =RC[-2]+RC[-1]

Example of using FormulaR1C1 to set formula

In worksheet, by default, Excel convert your formula to R1C1 but you cannot see it unless you enable “R1C1 Reference Style”.  Therefore, type the below in worksheet is equivalent to adding R1C1 style.

Excel_formulaR1C1_01

In VBA

Formula Return Explanation
Range(“C2”).FormulaR1C1 = “=RC[-2]” 1 Refer to the cell 2 columns left
Range(“C2”).FormulaR1C1 = “=R[1]C[-2]” 2 Refer to the cell 1 row down and 2 columns left

Recommended Readings

Excel worksheet R1C1 reference style

 Outbound References

http://msdn.microsoft.com/en-us/library/office/ff823188%28v=office.15%29.aspx

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *