Excel 2010+ VBA - 如何搜索范围的公式而不是值

Question

我需要使用 Excel VBA 搜索一系列单元格，返回第一个匹配的行号。只要我搜索值，使用 Match 函数就很容易做到这一点。但我需要搜索公式，而不是值。

例如，当我搜索“=A4+2”时，我需要 VBA 返回“4”...

Answer 1

你可以直接使用match

Application.Match("=A4+2", Range("B1:B5").Formula)

会给你4

编辑

您可能会收到来自 Match 的 255 个字符限制的错误。您也可能想使用工作表内的输出。只需将此代码放入模块中即可:

Public Function MATCHFUNC(str As String, rng As Range, Optional fOnly As Boolean, Optional fAdr As Boolean) As Variant
  Dim i As Long, runner As Variant
  If UBound(rng.Value, 1) > 1 And UBound(rng.Value, 2) > 1 And Not fAdr Then MATCHFUNC = 0: Exit Function
  For Each runner In rng
    i = i + 1
    If Not fOnly Or (runner.Text <> runner.Formula) Then
      If InStr(1, runner.Formula, str, 1) Then
        If fAdr Then MATCHFUNC = runner.Address Else MATCHFUNC = i
        Exit Function
      End If
    End If
  Next
  MATCHFUNC = 0
End Function

您现在可以像普通工作表功能一样使用它。以您的图片为例:
MATCHFUNC([要搜索的字符串],[要查找的范围],[1 仅在包含公式的单元格中查找],[1 以 $A$1 格式获取地址])

=MATCHFUNC("+2",B3:B5)     = 1      - it was found in the first cell
=MATCHFUNC("2",B1:B5)      = 2      - "2" is also in B2
=MATCHFUNC("2",B1:B5,1)    = 3      - B2 will be skipped - formulas only
=MATCHFUNC("+2",B3:B5,,1)  = "$B$3" - address of the first cell with match
=MATCHFUNC("9",B1:B5)      = 0      - not found in range
=MATCHFUNC("2",A1:B5)      = 0      - range needs to be only 1 row or 1 column without fAdr
=MATCHFUNC("2",A1:B5,,1)   = "$B$2" - check goes A1->B1...->A2->B2...

对于这样的特殊情况，您可能需要使用fAdr = 1:

=ROW(INDIRECT(MATCHFUNC("2",B4:B5,,1)))  = 4 - absolute row of the first cell with match

假设您出于某种原因不想检查 B1:B3，但您需要绝对行。

您仍然可以在 VBA 本身中使用它，例如:iVal = MATCHFUNC("=B", Range("B4:B5"))
此外，函数本身可以轻松改进，以在一次运行中输出数组或检查不同的字符串或执行任何您想要的操作(如果不需要，您也可以跳过 2 个可选部分以保持快速且易于理解): )