在VBA中使用xLookUp返回值的范围

Question

我正在尝试在VBA中使用Xlookup。我以前曾在Excel工作表中将其用作公式，但在VBA中，我一直坚持使用它来返回一系列值。

例如，由于Xlookup仅返回单个值，即B列中的值，因此下面的代码可以很好地工作：

Range("E1") = Application.WorksheetFunction.XLookup(sValue, Range("A:A"), Range("B:B"), "Tag Not Found", 0, 1)

一旦我将返回范围增加到超过一列，我就得不到任何返回：

Range("E1") = Application.WorksheetFunction.XLookup(sValue, Range("A:A"), Range("B:C"), "Tag Not Found", 0, 1)

在Excel中，这个公式只是将第一个值放在E1中，然后将第二个值‘溢出’到F1中。

在后一个例子中，我看到返回的类型是变体(1到2)。有没有什么技巧可以让它表现得像Excel一样，并简单地溢出？

我想我可以把返回值放到一个变量中，然后遍历它并提取每个元素-但我想知道是否有更干净的方法来做到这一点。

Answer 1

要使事物动态溢出，您需要一个公式。XLOOKUP的一个诀窍是它可以返回一个范围，而不仅仅是值。所以试着使用这个公式；

Range("e1").Formula2 = "=" & Application.WorksheetFunction.XLookup(2, Range("A:A"), Range("B:C")).Address

Answer 2

将在VBA中使用工作表函数的结果返回给变量总是一个好主意。

这样做的主要原因是检查错误，但另一个原因是结果可能在一个数组中返回多个值

当返回一个数组时，您可以使用UBound来获取行数和列数，并适当地调整目标范围的大小。

Dim Res As Variant

    Res = Application.XLookup(sValue, Range("A:A"), Range("B:C"), "Tag Not Found", 0, 1)

    If Not IsError(Res) Then
        If Not IsArray(Res) Then
            Range("E1").Value = Res
        End If
        Range("E1").Resize(UBound(Res,1), UBound(Res,2)).Value = Res
    Else
        MsgBox "XLOOKUP returned an error!", vbInformation
    End If