excel - 在 Excel VBA VLookup 函数中使用索引和匹配

Question

我有一个我经常使用的 Excel 公式，它执行 VLookup .它有一个嵌入式 Index/Match给出“M”列中的最后一个数值。
工作表公式是这样的:

=VLOOKUP(INDEX($M$10:$M75,MATCH(9.99999999999999E+307,$M$10:$M75)),Data,4)

手机 $M75作为该公式所在行的单元格。M 列中有数字、非数字和空白单元格，但我想要的 ID 始终是数字。

所以我正在尝试编写一个自定义函数，让我可以简单地编写 =current()
这是我所拥有的:

Function Current()
  Dim LookupRange As Variant
  Dim indexVar As Variant
  LookupRange = Range("$M$1:M" & ActiveCell.Row)
  indexVar = Application.Index(Range(LookupRange), Application.Match(9.99999999999999E+307, Range(LookupRange)))
  Current = Application.WorksheetFunction.VLookup(indexVar, Worksheets("Info").Range("Data"), 4)
End Function

我尝试过使用不同的变量类型(String、Long、Variant)，但我似乎无法让函数工作。
我不知道这是否足够的信息，但任何人都可以看到我是否遗漏了什么？
我只得到 #VALUE!在 Windows 7 上使用 Excel 2013

Answer 1

此代码存在一些与您的描述不一致的问题。

LookupRange是一个变体数组。当我对此进行测试时，它会引发 1004 Method 'Range' of object '_Global' failed分配给 indexVar 的错误.

如果我 Dim LookupRange As String根据您的评论，我还收到了 Type Mismatch错误。

由于 LookupRange应该是一个范围，我将它声明为一个范围，并使用 Set赋值语句中的关键字。

Set lookupRange = Range("$M$10:$M" & ActiveCell.Row)

您对 lookupRange 的作业中可能存在拼写错误.在您的公式中，最初您使用 $M$1 , 但在你使用的函数中 $M$10 .

如果您从 $M$10 开始此范围，并尝试在第 1-9 行之间的任何单元格中计算公式，您将收到错误消息。

在相关说明中， 如果没有数值 在 lookupRange ，这将返回一个错误，例如，将 Current()在单元格中 M2查找范围为 M1:M2 ，其中我没有数字数据。



我不确定您要如何处理此问题，但我的回答包括避免该错误的一种方法。您可以根据需要进行修改。最后:

依托ActiveCell.Row似乎是个坏主意，因为此公式可能会重新计算并产生不希望的结果。

相反，将此作为公式的必需参数，然后您可以从工作表中调用它，例如: =Current(Row()) .

综上所述，我认为这应该可行，并且我为未找到匹配错误提供了一个示例/转义。
:

Public Function Current(r As Long)
    Const bigNumber As Double = 9.99999999999999E+307
    Dim wsInfo As Worksheet: Set wsInfo = Worksheets("Info")
    Dim lookupRange As Range
    Dim indexVar As Long
    Dim myVal As Variant

    Set lookupRange = Range("$M$1:$M" & r)
    If Not Application.WorksheetFunction.Sum(lookupRange) = 0 Then
        indexVar = Application.Index(lookupRange, Application.Match(bigNumber, lookupRange))
        myVal = Application.WorksheetFunction.VLookup(indexVar, wsInfo.Range("Data"), 4)
    Else:
        myVal = "No numbers found in: " & lookupRange.Address
    End If
    Current = myVal

End Function

评论更新

您可以添加 Application.Volatile link在变量声明之前。这应该强制重新计算:

whenever calculation occurs in any cells on the worksheet.

但是，当其他工作表上的值(例如，您的 Worksheets("Info") 是另一个工作表)更改时，这不会强制计算。

每次激活包含 =Current(Row()) 的工作表时强制重新计算函数，你可以把它放在工作表的代码模块中:

Private Sub Worksheet_Activate()
    Me.Calculate
End Sub

这可能与您所能得到的一样接近——复制原生 VLOOKUP没有实际使用 VLOOKUP 的功能公式。

附加说明:

偏爱正确/强类型变量，我声明 indexVar as Long并为 9.99999999999999E+307 创建一个 double 变量(使用这种方式更容易)。我还创建了一个工作表对象变量，我再次发现它们更易于使用。