在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中。

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

我想我可以把返回的值放入一个变量中，然后循环遍历它并提取每个元素——但我想知道是否有更干净的方法。

Answer 1

最好将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

Answer 2

要使事情动态地溢出，您需要一个公式。XLOOKUP的一个技巧是，它可以返回Range，而不仅仅是值。所以试着使用这个公式；

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