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

I need to search a range of cells using Excel VBA, returning the row number of the first match. This would be easy with the Match function, as long as I am searching values. But I need to search the formulas, not the values.

我需要使用Excel VBA搜索一系列单元格,返回第一个匹配的行号。只要我在搜索值,匹配功能就很容易。但我需要搜索公式,而不是值。

e.g.I need the VBA to return "4" when I search for "=A4+2"...

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

2 个解决方案

#1

you could do it directly with match

你可以直接用匹配来做

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

will give you 4

会给你4

EDIT

You may get errors cus of the 255-character-limit which comes from Match. Also you may want to use the output inside the worksheet. Simply put this code in module:

您可能会遇到来自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

You now can use it like a normal worksheet-function. As example with your picture:
MATCHFUNC([string to search for],[range to look in],[1 to look only in cells containing formulas],[1 to get the address in $A$1 format])

您现在可以像普通的工作表一样使用它。以您的图片为例: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...

You may want to use the fAdr = 1 for special cases like that:

对于这样的特殊情况,您可能希望使用fAdr = 1:

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

Asuming you don't want to check B1:B3 for whatever reason but you need the absolute row.

假设您不想因任何原因检查B1:B3,但您需要绝对行。

Still you also can use it in VBA itself like: iVal = MATCHFUNC("=B", Range("B4:B5"))
Also the function itself can easiely improved to also output arrays or check for different strings in one run or do whatever you want (if there is no need to, you also can skip the 2 optinal parts to keep it fast and easy to understand) :)

你还可以在VBA中使用它,如:iVal = MATCHFUNC(“= B”,Range(“B4:B5”))此外,函数本身也可以轻松改进,也可以输出数组或在一次运行中检查不同的字符串或做无论你想要什么(如果没有必要,你也可以跳过2个视光片部分以保持快速和易于理解):)

#2

If you want the address of the first found cell - this will work as a worksheet function (=FindFirst("=A",B2:B6)) and being called from another VBA procedure:

如果你想要第一个找到的单元格的地址 - 这将作为工作表函数(= FindFirst(“= A”,B2:B6))并从另一个VBA过程调用:

Public Function FindFirst(FindValue As String, InRange As Range) As Variant

    Dim rFound As Range

    With InRange

        Set rFound = .Find( _
            What:=FindValue, _
            After:=InRange.Cells(InRange.Cells.Count), _
            LookIn:=xlFormulas, _
            LookAt:=xlPart)

        If Not rFound Is Nothing Then
            FindFirst = rFound.Address
        Else
            FindFirst = CVErr(xlErrValue)
        End If

    End With

End Function

If, on the other hand you want all found cells you can use this - but note that it won't work as a worksheet function.

另一方面,如果你想要所有找到的单元格,你可以使用它 - 但请注意它不能用作工作表函数。

Public Sub Test()

    MsgBox FindInFormula("=A", ThisWorkbook.Worksheets("Sheet1").Range("B2:B6")).Address

End Sub

Public Function FindInFormula(FindValue As String, InRange As Range) As Range

    Dim rFound As Range
    Dim sFirstAdd As String
    Dim rReturnRange As Range

    With InRange

        Set rFound = .Find( _
            What:=FindValue, _
            After:=InRange.Cells(InRange.Cells.Count), _
            LookIn:=xlFormulas, _
            LookAt:=xlPart)

        If Not rFound Is Nothing Then
            sFirstAdd = rFound.Address
            Do
                If rReturnRange Is Nothing Then
                    Set rReturnRange = rFound
                Else
                    Set rReturnRange = Union(rReturnRange, rFound)
                End If
                Set rFound = .FindNext(rFound)
            Loop While Not rFound Is Nothing And rFound.Address <> sFirstAdd
        End If

    End With

    Set FindInFormula = rReturnRange

End Function

You'll need to update the procedures to return the address or a reference to the cell - adjust to your needs.

您需要更新程序以返回地址或对单元格的引用 - 根据您的需要进行调整。

#1