VBA Evaluate function and array formula returning range of values

Question

Suppose I want to use Evaluate function to evaluate an array formula which returns a range of values. For example I try to get index (using Excel function MATCH) in an ordered list in Sheet2!A:A for each values in Sheet1!A:A. And I want to put the indices in column B.

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

Whan I run the code, I get a column of repeated values - the values are equal to the index of the first element. This is not correct.

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

So my question: how to use Evaluate function returning a whole range of values?

Answer 1

Interesting issue. I was not able to get the MATCH function to return an array using VBA Evaluate. However, the following modification seems to work, using the zero (0) for the row argument in the index function returns all of the rows. Note also 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