FORMULA.ARRAY

Macro Sheets Only

Enters a formula as an array formula in the range specified or in the current selection. Equivalent to entering an array formula while pressing CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+ENTER in Microsoft Excel for the Macintosh.

Syntax

FORMULA.ARRAY(formula_text, reference)

Formula_text is the text you want to enter in the array. For more information on formula_text, see the first form of FORMULA.

Reference specifies where formula_text is entered. It can be a reference to a cell on the active worksheet or an external reference to a named workbook. Reference must be a R1C1-style reference in text form. If reference is omitted, formula_text is entered in the active cell.

Examples

If the selection is D25:E25, the following macro formula enters the array formula {=D22:E22+D23:E23} in the range D25:E25:


FORMULA.ARRAY("=R[-3]C:R[-3]C[1]+R[-2]C:R[-2]C[1]")

Regardless of the selection, the following macro formula enters the array formula {=D22:E22+D23:E23} in the range D25:E25:


FORMULA.ARRAY("=R[-3]C:R[-3]C[1]+R[-2]C:R[-2]C[1]", "R25C4:R25C5")

To use FORMULA.ARRAY to put an array in a specific workbook, specify the name of the workbook as an external reference in the reference argument. Using "[SALES.XLS]North!R25C3:R25C4" as the reference argument in the preceding example would enter the array in cells C25:D25 on the worksheet named North in the workbook SALES.XLS. Using "SALES!R25C3:R25C4" as the reference argument would enter the array in the same cells in the worksheet named SALES.

Related Functions

FORMULA Enters values into a cell or range or onto a chart

FORMULA.FILL Enters a formula in the specified range

List of Command-Equivalent Functions