Menu
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)
207
Enter an array formula Select the cells where you want to see your results. Enter your formula. Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.
FormulaArray property is used to set or return the array formula of a range and works in a very similar way to the more familiar Range.
An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.
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
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
