Insert an Array Formula via VBA

vba excel excel-formula array-formulas
Bramat picture Bramat · Dec 22, 2014 · Viewed 34k times · Source

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)

Answer

Gareth picture Gareth · Dec 22, 2014

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

Related questions

How to get min value in a group?

Example: ID Value MAX Group1 2 6 Group1 4 6 Group1 6 6 Group2 1 3 Group2 3 3 Group3 7 8 Group3 4 8 Group3 2 8 Group3 8 8 Group4 1 3 Group4 2 3 Group4 3 3 Group5 7 7 The column 'MAX' has the results I want. My two part question is: (1) How can I get the values for the 'Max' column? …

Read More
Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More
What's the quickest way to multiply multiple cells by another number?

If I have a block of cells such as A1:D5, what's the quickest way of multiplying each cell by another number, 1.1 using Microsoft Excel?

Read More