CODEWITHSUNDEEP
vbaexcelexcel-2013
jjjjjjjjjjj
jjjjjjjjjjj

Reputation: 447

Using Range() with array formulas in Excel 2013

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.

Upvotes: 1

Views: 212

Answers (2)

Scott Craner
Scott Craner

Reputation: 152495

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

Upvotes: 1

Bad_Mama_Jama
Bad_Mama_Jama

Reputation: 196

Try changing the FormulaArray to FormulaR1C1

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

Thanks!!!

Upvotes: 1

Related Questions