Insert an Array Formula via VBA

17
votes

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)
1
vbaexcelexcel-formulaarray-formulas
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

1 Answers

27
votes

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