Formula Search (is it possible?)

  • Thread starter Thread starter dotban
  • Start date Start date
D

dotban

Is there a way to search a formula, NOT the value of the formula?

For example, formula in cell A1 is =7+5

Value displayed is 12

Can I search cell A1 for 7 or 5?

Thanks..
 
If you're asking about using the Edit/Find menu function, choosing
Formulas in the Find dialog's Look In dropdown will look at formulas and
values.


If you're asking about a worksheet function, here's one way using a UDF:

Public Function FormulaFind( _
sText As String, _
rRange As Range) As Variant
If rRange.Count = 1 Then
If rRange.HasFormula Then _
FormulaFind = InStr(rRange.Formula, sText)
Else
FormulaFind = CVErr(xlErrValue)
End If
End Function

Call as

=FormulaFind("7",A1)

The function returns 0 if sText isn't found, or the cell doesn't contain
a formula. If for some reason you want to mimic XL's FIND function, use

Public Function FormulaFind( _
sText As String, _
rRange As Range) As Variant
Dim bValid As Boolean
If rRange.HasFormula Then
FormulaFind = InStr(rRange.Formula, sText)
bValid = FormulaFind > 0
End If
If Not bValid Then FormulaFind = CVErr(xlErrValue)
End Function


If you're unfamiliar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Edit > Find > "Find what: 7" > Find All..........

The results window will display the formula in A1

Vaya con Dios,
Chuck, CABGx3
 
Back
Top