## To totally unlock this section you need to Log-in

Login

## Question

Explain the difference between the **excelrange.formula** and **excelrange.FomulaR1C1** in Microsoft Excel...

## Answer

In VBA **Formula** returns the formula in **A1 **notation, **FormulaR1C1 **returns the formula in **R1C1 **notation.

By default, **Excel **uses the **A1 **reference style, which refers to columns with letters (**A through XFD**) and refers to rows with numbers (**1 through 1048576**). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, **D50 **refers to the cell at the intersection of **column D** and **row 50**.

In the **R1C1 **reference style, both the rows and the columns on the worksheet are numbered. In the R1C1 style, Excel indicates the location of a cell with an "**R**" followed by a **row **number and a "**C**" followed by a **column **number.

For example, the absolute cell reference **R1C1 **is equivalent to the absolute reference **$A$1** in **A1 **reference style.

## Example

This example illustrates the difference between **A1**, **R1C1 **and **R[1]C[1]** style in Excel VBA.

### A1 Style

Place a command button on your worksheet and add the following code line (**A1 style**):

Range("D4").Formula = "=B3*10"

Result:

### R1C1 Style

Add the following code line (R1C1 style):

Range("D4").FormulaR1C1 = "=R3C2*10"

Result:

**Explanation**: cell **D4 **references cell B3 (*row 3, column 2*). This is an absolute reference ($ symbol in front of the row number and column letter).

### R[1]C[1] Style

Add the following code line (R[1]C[1] style):

Range("D4").FormulaR1C1 = "=R[-1]C[-2]*10"

Result:

**Explanation**: cell **D4 **references cell **B3 **(*one row above and 2 columns to the left*). This is a relative reference. This code line gives the exact same result as the code line used at **A1 style**.

Why learning about this? Because the **Macro Recorder** uses the **FormulaR1C1 **property (**R[1]C[1] style**). The **Macro Recorder** in Excel creates the following code lines if you enter the formula **=B3*10** into cell **D4**.

Difference between Formula and FormulaR1C1 in Microsoft Excel – http://heelpbook.altervista.org/2014/difference-formula-formular1c1-microsoft-excel/ #howto #microsoft #office @heelpbook #guide #formula