在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 a1004 Method 'Range' of object '_Global' failed
error in the assignment toindexVar
.- If I
Dim LookupRange As String
per your comments, I also get aType 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.Volatile
link 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屋!