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.
假设我想使用Evaluate函数来计算返回一系列值的数组公式。例如，我尝试在Sheet2的有序列表中获取索引（使用Excel函数MATCH）！A：A为Sheet1中的每个值！A：A。我想把索引放在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.
当我通过在工作表{= MATCH（A1：A10，Sheet2！A：A）}中放置数组公式来尝试相同时，它可以正常工作并返回每个元素的正确索引。
So my question: how to use Evaluate function returning a whole range of values?
所以我的问题：如何使用Evaluate函数返回一系列值？
2 个解决方案
#1
5
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.
有趣的问题。我无法使用MATCH函数返回使用VBA Evaluate的数组。但是，以下修改似乎可行，使用索引函数中的row参数的零（0）返回所有行。另请注意，我将match_type参数添加到Match函数中。
sh.Range("B1:B10").Value = sh.Evaluate("=INDEX(MATCH(A1:A10,Sheet2!A:A,0),0,1)")
#2
2
If Evaluate() does not make you happy, then:
如果Evaluate（）不能让你开心，那么：
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
#1
5
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.
有趣的问题。我无法使用MATCH函数返回使用VBA Evaluate的数组。但是，以下修改似乎可行，使用索引函数中的row参数的零（0）返回所有行。另请注意，我将match_type参数添加到Match函数中。
sh.Range("B1:B10").Value = sh.Evaluate("=INDEX(MATCH(A1:A10,Sheet2!A:A,0),0,1)")
#2
2
If Evaluate() does not make you happy, then:
如果Evaluate（）不能让你开心，那么：
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