HELP: Search a formula inside of a Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael Kintner - 14 Jan 2008 13:23 GMT

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

Reply to this Message

Joel - 14 Jan 2008 13:33 GMT

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

> 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.
[quoted text clipped - 9 lines]
> Thank you in advance for your help!!!
> Mike

Reply to this Message

Gary''s Student - 14 Jan 2008 13:34 GMT

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

Signature

Gary''s Student - gsnu2007c

Reply to this Message

Mike H - 14 Jan 2008 13:38 GMT

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

Reply to this Message