☰

如何在 Excel VBA 中一起实现 MATCH 和 INDEX 函数？

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 microsoft-excel-2010 worksheet-function vba
如何在 Excel VBA 中一起实现 MATCH 和 INDEX 函数？

我正在尝试查找第一个小于零的单元格的行号。因此，通过浏览网络，我能够得出这个公式，它完全有效：

=MATCH(TRUE,INDEX($C2531:$C2731 < 1,0),0)

但我想在 Excel VBA 中实现这一点，我尝试了几个选项，但出现类型不匹配错误 13，有人可以帮我吗？

With Application.WorksheetFunction
    Range("C2527").Value = .Match(True, .Index(Range("C2531:C2731") < 1, 0), 0)
End With

提前致谢。

答案1

据我了解，您在 INDEX 公式中使用了交集运算符（空格）："$C2531:$C2731 <1"。这会将一个范围 ($C2531$C2731) 中的每个单元格与另一个范围进行比较，但在本例中，使用的是 "<1" 比较。因此，交集将返回一个包含每个比较的 TRUE 或 FALSE 值的范围（即数组公式）。然后，MATCH 将查看结果范围以查找 "TRUE" 值。

换句话说，公式在幕后相对复杂，虽然读起来很短，但在 VBA 中，您希望从“幕后”角度保持事情尽可能简单明了 - 否则您会遇到问题。您要实现的目标很简单，但将 INDEX 和 MATCH 函数塞进 VBA 却不简单。

在 Visual Basic 中，没有原生的交集运算符。据我所知，唯一与之等同的是 Application.Intersect 函数，该函数以 Range 对象为参数。不幸的是，虽然您可以将第一个范围创建为“Set myRange = Range("$C2531:$C2731")，但我无法在 VBA 中创建“<1”的 Range 对象，因为这不是有效的单元格范围。因此，Excel 公式中的交集运算符似乎比 VBA 中的 Application.Intersect 方法更强大/更灵活。这就是这里的失败点（“$C2531:$C2731 <1”不是有效的 VBA）

现在，您可以花一天时间尝试看看是否可以在 VBA 中将范围与“<1”相交，但在 VBA 中有更简单的方法可以做到这一点。如果是我，我不会使用 Worksheet 函数，而只是使用标准的 For...Next 循环遍历您的行，并在简单的 Visual Basic 中对范围中的每个单元格进行比较。如果一个比较匹配，则返回行号。

为了得到一个想法，它看起来像这样：

Dim myRange As Range
Set myRange = Range("$C2531:$C2731")
For y = 1 to myRange.Cells.Rows.Count
   If y < 0 Then 
       result = myRange.Cells(y,1).Row
   End
Next y

答案2

您可以简单地将公式作为字符串发送。以下是示例。

Range("C2527").Value = "=MATCH(TRUE,INDEX($C2531:$C2731 < 1,0),0)"

相关内容