Excel 2010+ VBA -如何搜索范围的公式而不是值
我需要使用Excel搜索一系列单元格，返回第一个匹配的行号。只要我在搜索值，匹配函数就很容易做到这一点。但我需要搜索公式，而不是数值。
当我搜索“=A4+2”时，我需要VBA返回"4“。
回答 2
Stack Overflow用户
发布于 2015-12-10 14:16:24
你可以直接用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([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("+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"))
此外，函数本身也可以很容易地进行改进，也可以在一次运行中输出数组或检查不同的字符串，或者做任何您想做的事情(如果没有必要，也可以跳过两个视距部分，以保持它的快速性和易懂性) :)
Stack Overflow用户
发布于 2015-12-10 17:11:29
如果您想要第一个找到的单元格的地址--这将作为一个工作表函数(=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另一方面，如果您想要找到所有的单元格，您可以使用它-但是请注意，它不会作为工作表函数工作。
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您需要更新程序以返回地址或对单元格的引用，以适应您的需要。
https://stackoverflow.com/questions/34204060
复制相似问题
腾讯云开发者
