在Excel中使用索引和匹配VBA VLookup功能 [英] Using Index and Match in Excel VBA VLookup function

问题描述

我有一个excel公式，我经常使用一个 VLookup 。它有一个嵌入的索引/匹配，它给出了M列中的最后一个数值。

工作表公式是这样的：

  = VLOOKUP（INDEX（$ M $ 10：$ M75，MATCH （9.99999999999999E + 307，$ M $ 10：$ M75）），数据，4）
  

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

所以我试图编写一个自定义函数，让我简单地写下 = current（）

这是我有的：

 函数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，长，变体），但我似乎无法使功能工作。

我不知道这是否足够的信息，但任何人都可以看到我是否缺少某些东西？

我只得到 #VALUE！

在Windows 7上使用Excel 2013

解决方案

此代码有一些与您的描述不符的问题。

• LookupRange 是一个变体数组。当我测试这个，它引发了一个 1004方法'范围'的对象'_Global'失败错误在赋值给$ code> indexVar 。


• 如果我的 Dim LookupRange As String 根据您的意见，我还得到一个类型不匹配错误



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

  Set lookupRange = Range（$ M $ 10：$ M& ActiveCell.Row）
  

• 您可以输入到 lookupRange 中的拼写错误。在您的公式中，您最初使用 $ M $ 1 ，但在您使用的功能 $ M $ 10 。

如果您在 $ M $ 10 中启动此范围，并尝试评估公式在第1-9行之间的任何单元格，您将收到错误。

在相关注释中，如果 lookupRange ，这将返回一个错误，例如，将 Current（）放在单元格 M2 查找范围 M1：M2 ，其中我没有数字数据。

< img src =https://i.stack.imgur.com/gEpDj.jpgalt =在这里输入图像描述>

我不知道你怎么样想要处理这个，但我的回答包括一种避免这个错误的方法。您可以根据需要进行修改。最后：

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

相反，将此作为公式的必需参数，然后您可以从工作表中调用，如： = Current（Row（））。

把它放在一起，我认为这应该工作，我提供一个例子/匹配找不到错误。
：

 公共函数Current（r As Long）
 Const bigNumber As Double = 9.99999999999999E + 307 
 Dim wsInfo As Worksheet：设置wsInfo =工作表（Info）
 Dim lookupRange As Range 
 Dim indexVar As Long 
 Dim myVal As Variant 
 
 Set lookupRange = Range（$ M $ 1：$ M& r）
如果不是Application.WorksheetFunction.Sum（lookupRange）= 0然后
 indexVar = Application.Index（lookupRange，Application.Match bigNumber，lookupRange））
 myVal = Application.WorksheetFunction.VLookup（indexVar，wsInfo.Range（Data），4）
其他：
 myVal =没有找到的数字：& ; lookupRange.Address 
如果
当前= myVal 
 
结束函数
  

从评论更新

您可以添加 Application.Volatile 在变量声明之前，href =http://msdn.microsoft.com/en-us/library/office/aa213653(v=office.11​​).aspx =nofollow noreferrer>链接。这应该强制重新计算：

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

但是，如果其他工作表中的值（例如，您的工作表（Info）是另一个工作表，则不会强制计算）更改。

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

  Private Sub Worksheet_Activate（）
 Me.Calculate 
 End Sub 
  

这可能与您可以得到的一样 - 复制本机 VLOOKUP 功能，而不实际使用 VLOOKUP 公式。

其他注意事项：

正确/强类型变量的收益，我将 indexVar声明为Long 并创建 9.999999999的双精度变量99999E + 307 （这样更容易使用）。我也创建一个工作表对象变量，再次，我发现它们更容易使用。

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

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

Cell $M75 being 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.

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

Here is what I have:

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

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

解决方案

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

• LookupRange is a variant array. When I test this, it raises a 1004 Method 'Range' of object '_Global' failed error in the assignment to indexVar.
• If I Dim LookupRange As String per your comments, I also get a Type Mismatch error.

Since LookupRange should be a range, I declare it as a range, and use the Set keyword in the assignment statement.

Set lookupRange = Range("$M$10:$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.

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.

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

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.Row seems like a bad idea, since this formula may recalculate with undesired results.

Instead, make this a required argument for the formula, which you can then call from the worksheet like: =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$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

UPDATE FROM COMMENTS

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

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.

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

Private Sub Worksheet_Activate()
    Me.Calculate
End Sub

This is probably as close as you can get -- to replicating the native VLOOKUP functionality without actually using a VLOOKUP formula.

Additional notes:

Favoring correctly/strongly-typed variables, I declare indexVar as Long and 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.

这篇关于在Excel中使用索引和匹配VBA VLookup功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！