Excel，尝试索引，匹配，使用 VBA 查找值

Question

我正在尝试使用 VBA 查找单元格位置的值，在询问这个问题之前我已经搜索了 google 和 stackoverflow，因为我似乎无法让它工作。

以下是我尝试使用的代码，请注意，budgetcode 引用包含第一列中的代码之一的单元格，mo 引用包含数字 (1-12) 或包含短代码 (ytd) 的单元格、1 季度、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

这是我想要查找的部分数据:

                                       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

这是单元格中有效的代码，但我需要在 VBA 中执行。 (我需要在 vba 中执行的原因是有时预算代码将包含 2 个以上以逗号分隔的引用，我将使用 vba 来分隔它们并独立查找每个引用。)

=INDEX(Budget!$G$1:$X$5000,MATCH($F12,Budget!$B$1:$B$5000,0),MATCH(AN$1,Budget!$G$1:$X$1,0))

非常感谢您的帮助，我已经这样做了 3 天。

谢谢

以诺

Answer 1

问题在于Function 参数类型。

当您使用 mo = cell AN1 调用函数时，其中包含数字 1，即函数类型将其转换为 String "1"，该字符串不存在于 Budget!$G$1:$X$1 范围内，因为这些也是数字。

解决方案是使用Variant作为函数参数类型。

为了更轻松地调试此类错误，请尽量不要在一行代码中执行过多操作。将行拆分为 2 个 Match 函数和一个 Index，可以让您看到第二个匹配返回错误。

其他几点:

• 如果您将此函数作为UDF 调用(即从工作表单元格调用)，最好不要对范围进行硬编码。正如所写，当任何数据发生变化时，对 eBudgetl 的调用不会自动重新计算。
• Application 对象具有 Index 和 Match 版本，因此不需要 WorksheetFunction 调用<

重构版本来演示:

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

称为

=eBudgetl(Budget!$G$1:$X$5000,Budget!$B$1:$B$5000,Budget!$G$1:$X$1,$F12,AN$1)