VBA Compute a function and array formula that returns a range of values

Question

Suppose I want to use the Evaluate function to calculate an array formula that returns a range of values. For example, I'm trying to get an index (using the Excel MATCH function) in an ordered list in Sheet2! A: A for each value in Sheet1! A: A. And I want to put indexes in column B.

Dim sh as Worksheet Set sh = Sheets("Sheet1") sh.Range("B1:B10").Value = sh.Evaluate("=MATCH(A1:A10,Sheet2!A:A)") 

When I run the code, I get a column with duplicate values ​​- the values ​​are equal to the index of the first element. This is not true.

When I try to do the same by placing the array formula on the worksheet {= MATCH (A1: A10, Sheet2! A: A)}, it works without problems and returns the correct index for each element.

So my question is: how to use the Evaluate function that returns the whole range of values?

Answer 1

An interesting problem. I was not able to get the MATCH function to return an array using VBA Evaluate. However, the following modification seems to work, using zero (0) for the row argument in the index function, returns all rows. Note that I added the match_type argument to the Match function.

 sh.Range("B1:B10").Value = sh.Evaluate("=INDEX(MATCH(A1:A10,Sheet2!A:A,0),0,1)") 

Answer 2

If Evaluate () does not make you happy, then:

 Sub marine() Dim sh As Worksheet, r As Range Set sh = Sheets("Sheet1") Set r = sh.Range("B1:B10") r.FormulaArray = "=MATCH(A$1:A$10,Sheet2!A:A,0)" r.Copy r.PasteSpecial xlPasteValues End Sub