Search actual Formula for cell ref?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Other than using Find from the Edit menu, is there any way of (using
formula) searching another formula for a certain cell reference?

Let's say I have a single column (B) of 100 similar formulas. I want to
check that none of the formulas contain a reference to column H, so want
a formula I can enter in the adjacent cell to flag if it does, i.e.

= IF (columnB_formula_contains_a_ref_to_column_H, "Y","")

I've been using Edit-Find to look for !H in the formulas but want to do
it using functions as can see some useful applications of the technique
(if it exists).

Any help would be great ....... Rgds, Jason
 
Jay,

There are no worksheetfunction that will do this, but you could use a UDF:

Function ConRef(myR As Range, ColLet As String) As Boolean
ConRef = False
If InStr(1, Application.ConvertFormula( _
myR.Formula, xlA1, xlA1, xlAbsolute), _
"$" & ColLet) > 1 Then ConRef = True
End Function

used like

=ConRef(B1,"H")

to return TRUE or FALSE.

HTH,
Bernie
MS Excel MVP
 
Jay
You will have to use VB for this. The following is a UDF (user defined
Function).
Copy this into a VB Module, and Enter it like this =checkformula("H",b26)
where H is the value to look for and B26 contains the formula to inspect.

Function CheckFormula(ByVal ref As String, rng)
Dim tmp As String, n
tmp = rng.Formula
On Error GoTo ErrorHandler
n = WorksheetFunction.Search(ref, tmp)
If IsNumeric(n) Then
CheckFormula = "Y"
Exit Function
End If
ErrorHandler:
CheckFormula = ""
Exit Function
End Function

I did not hard code "H" as you might look for something else in the future.

Regards
Peter
 
Back
Top