VBA评估函数和数组公式返回值的范围 [英] VBA Evaluate function and array formula returning range of values

问题描述

假设我想使用Evaluate函数来评估返回值范围的数组公式。例如，我尝试在Sheet2中的有序列表中获取索引（使用Excel函数MATCH）！A：A对于Sheet1！A中的每个值A：A。我想把索引放在B列中。

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.

当我通过将数组公式放在工作表{= MATCH（A1：A10，Sheet2！A：A）}中，没有问题，并为每个元素返回正确的索引。

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.

所以我的问题：如何使用Evaluate函数返回一系列值？

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

推荐答案

有趣的问题。我无法使用VBA Evaluate获取MATCH函数返回数组。但是，以下修改似乎可以正常工作，在索引函数中使用零（0）作为行参数返回所有行。另请注意，我将match_type参数添加到Match函数中。

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

这篇关于VBA评估函数和数组公式返回值的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！