To totally unlock this section you need to Log-in
Explain the difference between the excelrange.formula and excelrange.FomulaR1C1 in Microsoft Excel...
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.
This example illustrates the difference between A1, R1C1 and RC style in Excel VBA.
Place a command button on your worksheet and add the following code line (A1 style):
|Range("D4").Formula = "=B3*10"|
Add the following code line (R1C1 style):
|Range("D4").FormulaR1C1 = "=R3C2*10"|
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).
Add the following code line (RC style):
|Range("D4").FormulaR1C1 = "=R[-1]C[-2]*10"|
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 (RC style). The Macro Recorder in Excel creates the following code lines if you enter the formula =B3*10 into cell D4.