VBA Evaluate function and array formula returning range of values

5
votes

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?

2
vbaexcel
Try Evaluate("=TRANSPOSE(MATCH(A1:A10,Sheet2!A:A))") or Application.Transpose(Evaluate("=MATCH(A1:A10,Sheet2!A:A)")) - Dmitry Pavliv
No, that does not work either - I tested it. I would be surprised if it worked because A1:A10 is a column of source values and B1:B10 is also a column. Transpose should not be necessary, I think. - V.K.

2 Answers

5
votes

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)")
2
votes

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