CODEWITHSUNDEEP
vbaexcelexcel-formulaarray-formulas
Bramat
Bramat

Reputation: 997

Insert an Array Formula via VBA

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)

Upvotes: 22

Views: 55553

Answers (2)

Jonathan Biemond
Jonathan Biemond

Reputation: 548

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

Upvotes: 1

Gareth
Gareth

Reputation: 5243

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

Upvotes: 30

Related Questions