HELP: Search a formula inside of a Cell

[Michael Kintner]

I am trying to compare some old spreadsheets and upgrade to the new and have
a case in try to determine if a value was added to a formula inside a cell.
So I would like to do a search/find on the contents of the FORMULA in the
cell.

Example: Cell A1 Contains a formula +F12+F34+F67

Is there a function that can search the formula? Example: FIND "F34" in
cell A1. Everything I can see so far you can only search the results, not
the formula.

Thank you in advance for your help!!!
Mike

 

[Joel]

formula are strings. Usually I use the FIND in the worksheet menu to locate
the formula instead of writing VBA code. You can use for if necessary

mystring = Range("A1").formula
if Instr(mystring,"F34") > 0 then
'enter your code here
end if

 

[Gary''s Student]

From the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/14/2008 by James Ravenswood
'

'
Cells.Find(What:="F34", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

 

[Mike H]

Michael,

Not sure how it could be done at worksheet level but here's a macro

Sub searchFormula()
Dim MyRange As Range
Set MyRange = Range("A1:B20")
For Each c In MyRange
If c.HasFormula Then
If InStr(1, c.Formula, "F34", vbTextCompare) > 0 Then
MsgBox "Formula at " & c.Address & " contains F34 "
End If
End If
Next


I think I'd prefer to make the search address a variable but that should be
an easy conversion.


Mike