Excel，尝试索引，匹配，以使用 VBA 查找值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23282913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel, Trying Index, Match, to lookup values with VBA
提问by user3566159
I am trying to look-up values at cell locations using VBA, I have searched google and all over stackoverflow before asking this because I can't seem to get it to work.
我正在尝试使用 VBA 在单元格位置查找值，在问这个之前我已经搜索了谷歌和整个 stackoverflow，因为我似乎无法让它工作。
The following is the code I am trying to use, note that budgetcode references a cell containing one of the codes in the first column and mo references a cell that contains a number (1-12) or contains a short code (ytd, 1qtr, 2qtr, 3qtr). Example is that I want to pull CAD-NS for February (2), and I should get 5666.40.
以下是我尝试使用的代码，请注意，budgetcode 引用包含第一列中一个代码的单元格，mo 引用包含数字 (1-12) 或包含短代码 (ytd, 1qtr, 2 季度，3 季度）。例子是我想拉2月（2）的CAD-NS，我应该得到5666.40。
Function eBudgetl(budgetcode As String, mo As String)
eBudgetl = Application.WorksheetFunction.Index(Range("Budget!G1:X5000"), _
Application.WorksheetFunction.Match(budgetcode, Range("Budget!B1:B5000"), 0), _
Application.WorksheetFunction.Match(mo, Range("Budget!G1:X1"), 0))
End Function
Here is part of the data I wish to lookup:
这是我希望查找的部分数据：
1 2 3 4
CAD-NS I Net Sales 5264.0 5666.4 5614.9 5966.6
COSMAT E Material 6207.5 3660.0 3661.9 3560.9
COSDL E Direct Labor 610.4 105.3 167.1 123.6
CAD-MOIL E Indirect Labor 671.2 163.4 181.6 161.7
CAD-MOSAL E Salary Overhead 601.0 106.0 101.0 101.0
Here is the code in the cell that works, but I need to do in VBA. (The reason I need to do in vba is sometimes the budgetcode will contain 2+ references separated by a comma and I am going to use vba to separate them and look each up independently.)
这是单元格中有效的代码，但我需要在 VBA 中执行。（我需要在 vba 中做的原因是有时预算代码将包含 2+ 个用逗号分隔的引用，我将使用 vba 将它们分开并独立查找。）
=INDEX(Budget!$G:$X00,MATCH($F12,Budget!$B:$B00,0),MATCH(AN,Budget!$G:$X,0))
I appreciate any help very much, I have been at this for 3 days now.
我非常感谢任何帮助，我已经在这里呆了 3 天了。
Thanks,
谢谢，
Enoch
以诺
采纳答案by chris neilsen
The issue is the Functionparameter types.
问题是Function参数类型。
When you call your Function with mo= cell AN1, containing the number1, the Function type casts it to the String"1", which doen't exisit in the range Budget!$G$1:$X$1, since these are also numbers.
当您使用包含数字的mo= cell调用您的 Function 时，Function 类型会将其强制转换为String，该字符串在范围内不存在，因为这些也是数字。AN11"1"Budget!$G$1:$X$1
The solution is to use Variantas the function paramters type.
解决方案是Variant用作函数参数类型。
To make debugging this type of error easier, try not to do too much in a single line of code. Splitting the line into 2 x Matchfunctions and an Index, would allow you to see the second match return an error.
为了使调试此类错误更容易，请尽量不要在一行代码中做太多事情。将该行拆分为 2 个 xMatch函数和一个Index, 将允许您看到第二个匹配返回错误。
Couple of other points:
其他几点：
- If your are calling this function as a UDF(ie called from a worksheet cell), it is better not to hard code the ranges. As written, the calls to
eBudgetlwould not automatically recalculate when any of their data changes. - the
Applicationobject has a version ofIndexandMatchso theWorksheetFunctioncalls are not required
- 如果您将此函数作为UDF调用（即从工作表单元格调用），最好不要对范围进行硬编码。正如所写的那样，
eBudgetl当它们的任何数据发生变化时，调用不会自动重新计算。 - 该
Application对象有一个版本，Index并Match因此WorksheetFunction呼吁不需要
Refactored version to demonstrate:
重构版本以演示：
Function eBudgetl(rData As Range, rBudCode As Range, rMo As Range, budgetcode As Variant, mo As Variant)
Dim rw As Variant
Dim col As Variant
With Application
col = .Match(budgetcode, rBudCode, 0)
rw = .Match(mo, rMo, 0)
eBudgetl = .Index(rData, col, rw)
End With
End Function
Called as
称为
=eBudgetl(Budget!$G:$X00,Budget!$B:$B00,Budget!$G:$X,$F12,AN)
