The syntax of Excel FORMULAR1C1 and how to use it

Features of the ①formular1c1 property

Returns or sets the formula for the specified object, represented by the R1C1 format symbol in the macro language. Variant type, readable and writable.

②FORMULAR1C1 's Grammar

An expression. FormulaR1C1

An expression is a variable that represents a Range object.

Syntax description

If the cell contains a constant, this property returns the constant. If the cell is empty, this property returns an empty string. If the cell contains a formula, this property returns the formula as a string, in the same format as when it appears in the formula bar, including the equal sign.

If you set a value or formula for a cell's format to a date type, Microsoft Excel checks that the cell's format matches a date or time array format, and if not, the default short date number format is used.

If the specified range is a one-dimensional or two-dimensional area, you can specify the formula as an array of the same dimensions in Visual Basic. Similarly, you can use formulas in the Visual Basic array.

To set a formula for a multiple-cell range, all cells in the range are populated with this formula.

A simple example of ③FORMULAR1C1

Example one:

Worksheets ("Sheet1"). Range ("B1"). FORMULAR1C1 = "=sqrt (R1C1)"

To set a formula for the B1 cell of a SHEET1 worksheet, set the formula: "=sqrt (C2)

Example two:

Worksheets (1). Cells (3, 4). FORMULAR1C1 = "=average (Rc[-3]:rc[-2])"

The effect is to set the formula for the D3 cell in the worksheet numbered 1, which is set to the formula: =average (A3:B3)