CODEWITHSUNDEEP
vbaexcel
HiFile.app - best file manager
HiFile.app - best file manager

Reputation: 8029

VBA Evaluate function and array formula returning range of values

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?

Upvotes: 5

Views: 5376

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

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

Upvotes: 5

Gary's Student
Gary's Student

Reputation: 96773

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

Upvotes: 2

Related Questions