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

Using Index and Match in Excel VBA VLookup function

提问by Brad

I have an excel formula that I use often that does a VLookup. It has an embedded Index/Matchthat gives the last numeric value in the "M" Column.
The Worksheet formula is this:

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

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

Cell $M75being the cell of the row this formula is in. There are numeric, non-numeric and blank cells in Column M but the ID's that I want are always numeric.

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

So I am trying to write a custom function that would let me simply write =current()

所以我正在尝试编写一个自定义函数，它可以让我简单地编写 =current()

Here is what I have:

这是我所拥有的：

Function Current()
  Dim LookupRange As Variant
  Dim indexVar As Variant
  LookupRange = Range("$M: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

I have tried using different variable types (String, Long, Variant) but I can't seem to get the function to work.
I don't know if this is enough info but can anyone see if I am missing something?
I only get #VALUE!
Using Excel 2013 on Windows 7

我尝试使用不同的变量类型（String、Long、Variant），但我似乎无法使该函数正常工作。
我不知道这是否是足够的信息，但谁能看到我是否遗漏了什么？
我只能#VALUE!
在 Windows 7 上使用 Excel 2013

回答by David Zemens

There are some problems with this code that are at odds with your description.

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

• LookupRangeis a variant array. When I test this, it raises a 1004 Method 'Range' of object '_Global' failederror in the assignment to indexVar.
• If I Dim LookupRange As Stringper your comments, I also get a Type Mismatcherror.

• LookupRange是一个变体数组。当我对此进行测试时，它会1004 Method 'Range' of object '_Global' failed在对indexVar.
• 如果我Dim LookupRange As String根据您的评论，我也会收到Type Mismatch错误消息。

Since LookupRangeshould be a range, I declare it as a range, and use the Setkeyword in the assignment statement.

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

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

• Possible typo in your assignment to lookupRange. In your formula, originally you use $M$1, but in the function you use $M$10.

• 分配给lookupRange. 在您的公式中，最初您使用$M$1，但在您使用的函数中$M$10。

If you start this range at $M$10, and try to evaluate the formula in any cell between row 1-9, you will get an error.

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

On a related note, if there are no numeric valuesin the lookupRange, this will return an error, e.g., putting Current()in cell M2makes a lookup range of M1:M2, wherein I have no numeric data.

与此相关的，如果没有数值的lookupRange，这会返回一个错误，例如，将Current()在小区M2做的查找范围M1:M2，其中我有没有数值数据。

I am not sure how you want to handle this, but my answer includes one way to avoid that error. You can modify as needed. Finally:

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

• Relying on ActiveCell.Rowseems like a bad idea, since this formula may recalculate with undesired results.

• 依赖ActiveCell.Row似乎是一个坏主意，因为这个公式可能会重新计算出不想要的结果。

Instead, make this a required argument for the formula, which you can then call from the worksheet like: =Current(Row()).

相反，要对公式，然后你就可以从类似的工作表称这是一个必需的参数：=Current(Row())。

Putting it all together, I think this should work, and I provide an example/escape for the match not found error. :

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

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:$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

UPDATE FROM COMMENTS

评论更新

You can add Application.Volatilelinkbefore the variable declarations. This should force re-calculation:

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

whenever calculation occurs in any cells on the worksheet.

每当工作表上的任何单元格中发生计算时。

However, this will not force calculation when values on other worksheets (e.g., your Worksheets("Info")is another worksheet) change.

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

To force recalculation every time you active the sheet containing the =Current(Row())function, you could put this in the worksheet's code module:

要在每次激活包含该=Current(Row())函数的工作表时强制重新计算，您可以将其放在工作表的代码模块中：

Private Sub Worksheet_Activate()
    Me.Calculate
End Sub

This is probably as close as you can get -- to replicating the native VLOOKUPfunctionality without actually using a VLOOKUPformula.

这可能与您所能获得的最接近 -VLOOKUP无需实际使用VLOOKUP公式即可复制本机功能。

Additional notes:

补充说明：

Favoring correctly/strongly-typed variables, I declare indexVar as Longand create a double-precision variable for 9.99999999999999E+307(it's just easier to work with this way). I also create a worksheet object variable, again, I find them easier to work with.

倾向于正确/强类型变量，我声明indexVar as Long并创建了一个双精度变量9.99999999999999E+307（使用这种方式更容易）。我还创建了一个工作表对象变量，同样，我发现它们更易于使用。