Using Range() with array formulas in Excel 2013

Question

I have a VBA formula, let's say

Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaArray = "=MATCH(RC[-1],Sheet1!C1,0)"

When I run the macro it enters this into each cell

=MATCH(A2,Sheet1!A:A,0)

I want the RC[-1] argument to return cell A2, A3, A4, etc. It's currently returning A2 for all rows. How do I do this?

I also tried ActiveCell.FormulaArray and a for loop but it's far too slow for the number of rows I have.

If you want it to be entered as an array, which this simple Match is not, you will need to enter it into B2 first and then fill down to the bottom. .FormulaArray will treat it as if you highlighted the entire range and put the formula in the first cell then hit Ctrl-Shift-Enter which enters the same formula in all the cells. — Scott Craner
– Scott Craner, Commented Dec 9, 2016 at 20:30
@ScottCraner Is there a way to Ctrl-Shift-Enter all cells individually through VBA that does not involve looping through all cells? I tried a for loop but it's very slow, even slower than doing it manually. — jjjjjjjjjjj
– jjjjjjjjjjj, Commented Dec 9, 2016 at 20:38

Scott Craner · Accepted Answer · 2016-12-09 20:42:04Z

1

As stated in all the comments:

You will need to enter it into B2 first and then fill down to the bottom.

.FormulaArray will treat it as if you highlighted the entire range and put the formula in the first cell then hit Ctrl-Shift-Enter which enters the same formula in all the cells.

Range("B2").FormulaArray = "=MATCH(RC[-1],Sheet1!C1,0)"
Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).FillDown

answered Dec 9, 2016 at 20:42

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

2 Comments

jjjjjjjjjjj Over a year ago

It takes a very long time to run. It's something I'll have to deal with separately and optimize, but it works. Thank you.

Scott Craner Over a year ago

@jjjjjjjjjjj you may want to look into loading the data into memory array and do the calculations that your array formula is doing and store the output into another array then post it all at once. Array formulas are very consuming and depending on the number and reference data may even crash excel. I only use Array formula for few answers.

Bad_Mama_Jama · Accepted Answer · 2016-12-09 20:27:18Z

1

Try changing the FormulaArray to FormulaR1C1

Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=MATCH(RC[-1],Sheet1!C1,0)"

Thanks!!!

answered Dec 9, 2016 at 20:27

Bad_Mama_Jama

1962 gold badges2 silver badges11 bronze badges

5 Comments

jjjjjjjjjjj Over a year ago

Thanks, but it doesn't work because it's an array formula. How do I combine R1C1 and array notation?

Scott Craner Over a year ago

@jjjjjjjjjjj that simple match is not an array formula.

jjjjjjjjjjj Over a year ago

@ScottCraner It's not, but the real formula is. It's very long and not necessary to the question (beyond knowing it's an array formula) so I edited it for simplicity.

Scott Craner Over a year ago

@jjjjjjjjjjj how long? FormulaArray has a 255 character limit. Also see my comment above for your answer.

jjjjjjjjjjj Over a year ago

@ScottCraner It's around 200 characters.

Collectives™ on Stack Overflow

Using Range() with array formulas in Excel 2013

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related