Creating a UDF to search a cell's formula

[kittronald]

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.

 

[kittronald]

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.

 

[Ron Rosenfeld]

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
==========================

 

[kittronald]

Ron,

That works !

Thanks for the late night save.



- Ronald K.

 

[Ron Rosenfeld]

Ron,

That works !

Thanks for the late night save.



- Ronald K.

Glad to help. Thanks for the feedback.

 

[kittronald]

Ron,

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



- Ronald K.

 

[Ron Rosenfeld]

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)

 

[kittronald]

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.

 

[Ron Rosenfeld]

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.

You can use a similar algorithm but you would have to rewrite it as it expects ranges to be passed as argument.

 

[kittronald]

Ron,

I've been trying to get
=SEARCHFORMULA(Numbers,ActiveSheet.Names("Numbers_Formula").RefersTo) to
work.

However, given my limited development skills, it appears this is beyond
my ability.

Perhaps, there's another way.

Thanks for getting back to me.



- Ronald K.

 

[kittronald]

Ron,

I've tried the following, but the debugger keeps displaying a Run-
time error '13': Type mismatch.


Option Explicit
Function SEARCHFORMULA(find_text As Variant, within_text As Variant)
As Variant
Dim vFindText As Variant
Dim s As Variant
Dim i As Long
s = within_text
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


What is it that I'm not setting correctly ?



- Ronald K.

 

[Ron Rosenfeld]

Ron,

I've tried the following, but the debugger keeps displaying a Run-
time error '13': Type mismatch.


Option Explicit
Function SEARCHFORMULA(find_text As Variant, within_text As Variant)
As Variant
Dim vFindText As Variant
Dim s As Variant
Dim i As Long
s = within_text
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


What is it that I'm not setting correctly ?

Possibly find_text is not a range or an array. If it is not, then vFindText will not be an array, but rather just a simple variable.

 

[kittronald]

Ron,

When stepping through the code, the following line causes the error:

For i = LBound(vFindText) To UBound(vFindText)

When using SEARCHFORMULA as a UDF in a cell, Find_text is a named range
(i.e., Sheet1!$A$1:$A$5) and Within_text is the Refers to: field of a name.

The difference between using the SEARCHFORMULA UDF in a cell and in a
macro is the data type of Within_text.

The cell version uses a defined name for Within_text that contains a
text string (i.e. =CONCATENATE("SUM","PRODUCT")).

The macro version uses a name's Refers to: field.

How can I tell what data type is being expected ?




- Ronald K.

 

[kittronald]

Ron,

When stepping through the code, the following line causes the error:

For i = LBound(vFindText) To UBound(vFindText)

When using SEARCHFORMULA as a UDF in a cell, Find_text is a named range
(i.e., Sheet1!$A$1:$A$5) and Within_text is the Refers to: field of a name.

The difference between using the SEARCHFORMULA UDF in a cell and in a
macro is the data type of Within_text.

The cell version uses a defined name for Within_text that contains a
text string (i.e. =CONCATENATE("SUM","PRODUCT")).

The macro version uses a name's Refers to: field.

How can I tell what data type is being expected ?




- Ronald K.