Insert an Array Formula via VBA

Question

I'm using VBA, and I need to insert an array formula (the one that if I'm writing it manually, I'll press Ctrl+Shift+Enter and not just Enter). When I'm inserting it like a regular formula it doesn't work, neither when I put it with {} around it... What's the correct way of writing that formula using VBA?

The formula is this:

 =INDEX(subset!R1C1:R2472C10,MATCH(1,(RC1=subset!C1)*(RC2=subset!C2)*(RC5=subset!C5)*(RC6=subset!C6),0),10)

Assigning an array formula to a cell or range uses the Range("A1").FormulaArray = "<the formula>" syntax. It does not matter whether you have the formula in xlA1 or xlR1C1 format. — user4039065
– user4039065, Commented Dec 22, 2014 at 14:04

Gareth · Accepted Answer · 2020-01-31 11:24:48Z

30

You're looking for the FormulaArray property that you can set for a cell like so:

Range("A1").FormulaArray = "=INDEX(subset!R1C1:R2472C10,MATCH(1,(RC1=subset!C1)(RC2=subset!C2)(RC5=subset!C5)*(RC6=subset!C6),0),10)"

See the documentation here: http://msdn.microsoft.com/en-us/library/office/ff837104%28v=office.15%29.aspx

edited Jan 31, 2020 at 11:24

answered Dec 22, 2014 at 14:03

Gareth

5,2636 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tuntable Over a year ago

Very good. But how do you do it R1 C1 which is what I needed...

Jonathan Biemond · Accepted Answer · 2023-03-22 17:31:29Z

1

FormulaArray unfortunately does not preserve relative cell referencing when applied to a range as expected with FormulaR1C1. To keep the cell referencing relative in a vertical range you can use FillDown.

First enter the Array Formula in the first row of the range:

Range("A1").FormulaArray = "=SUM((R1C5:R2C10 = ""INCLUDE"") * RC5:RC10)

Then Fill Down the desired range:

Range("A1:A10").FillDown

answered Mar 22, 2023 at 17:31

Jonathan Biemond

5686 silver badges15 bronze badges

Collectives™ on Stack Overflow

Insert an Array Formula via VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related