I'd use a UDF, too!
Option Explicit
Function FormulaContains(rng As Range, myStr As String) As Boolean
Set rng = rng.Cells(1)
FormulaContains = False
If rng.HasFormula Then
FormulaContains _
= CBool(InStr(1, rng.Formula, myStr, vbTextCompare) 0)
End If
End Function
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Into a test cell and type:
=formulacontains(a1, "abc")
"T. Valko" wrote:
"Eric" wrote in message
...
Thank everyone for suggestions
Yes, I want to know if the string "ABC" is part of the *formula* and not
the
*result* of the formula?
InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK
It works when I insert a path in cell A1 and check it in cell B1, but it
does not work if I use this approach in another cell.
My case is that I need to search the path formula in B2 and check it on
cell
J1.
Do I need to change any following parameter in order to make it work for
my
case?
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric
This would be best done with a user defined function but I'm not the best
programmer!
If the target formula is in cell B2 and you want the extraction formula in
cell J1:
Change:
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
To:
=GET.CELL(6,INDIRECT("R[1]C[-8]",FALSE))
If the extraction formula is used for only this one specific cell reference,
Sheet1 B2:
=GET.CELL(6,Sheet1!$B$2)
Using the R1C1 reference style as I did gives it a little bit more
flexibilty as it can be used in other cells. Using absolute A1 reference
style limits its use to only one cell. Using relative A1 reference style can
lead to problems.
Biff
--
Dave Peterson