CODEWITHSUNDEEP
excelvbamatchformulas
John Joseph
John Joseph

Reputation: 1185

Excel 2010+ VBA - How can I Search a Range's Formulas Rather Than Values

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.

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

Upvotes: 2

Views: 647

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

you could do it directly with match

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

will give you 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:

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("+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: 

=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.

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) :)

Upvotes: 7

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

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:

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.

Upvotes: 0

Related Questions