I'm trying to find a way to search for text in a cell's formula.

Is this something that could be done with a UDF ?

For example ...

SearchFormula(find_text,within_text) where find_text could be a
single value or a multi-valued named range.

If A1 contained =VLOOKUP(One,$A:$B,2,FALSE)

and C1=One, C2=Two, C3=Three

and $C$1:$C$3 was a named range called "Numbers"

The formula would look like =SEARCHFORMULA(Numbers,A1) and would
return a Boolean value of 1.

If a match wasn't found, it would return a Boolean value of 0.

Any ideas ?




- Ronald K.

Made a mistake with the desired result.

The SEARCHFORMULA result should return the value found.

In the example above, if A1's formula contained the text "One", the
result should be "One".



- Ronald K.

On Sun, 24 Jul 2011 14:45:15 -0700 (PDT), kittronald wrote:

Made a mistake with the desired result.

The SEARCHFORMULA result should return the value found.

In the example above, if A1's formula contained the text "One", the
result should be "One".



- Ronald K.

Assuming you still want to return a zero if no match, then:

=========================
Option Explicit
Function SEARCHFORMULA(find_text As Range, within_text As Range) As Variant
Dim vFindText As Variant
Dim s As String
Dim i As Long
s = within_text.Formula
vFindText = find_text
SEARCHFORMULA = 0

For i = LBound(vFindText) To UBound(vFindText)
If InStr(1, s, vFindText(i, 1), vbTextCompare) 1 Then
SEARCHFORMULA = vFindText(i, 1)
Exit For
End If
Next i
End Function
==========================

Ron,

That works !

Thanks for the late night save.



- Ronald K.

On Sun, 24 Jul 2011 21:28:50 -0700 (PDT), kittronald wrote:

Ron,

That works !

Thanks for the late night save.



- Ronald K.

Glad to help. Thanks for the feedback.

Ron,

If you're still watching this thread, how could you use this
function within a macro ?



- Ronald K.

On Sun, 11 Sep 2011 18:00:55 -0700 (PDT), kittronald wrote:

Ron,

If you're still watching this thread, how could you use this
function within a macro ?



- Ronald K.

Just set your variable equal to the result of the formula:

v = SEARCHFORMULA(find_text As Range, within_text As Range)

Ron,

Thanks for the quick response.

Could this function be adapted to search a formula in a defined name's
Refers to: field ?



- Ronald K.