Searching for text within formula

[kittronald]

Is it possible to search the contents of a formula ?

For example, searching for the text "SUM" in A1 where A1 is
=SUM(1,2).

Using Search appears to only work with the result of a formula.

Is this something a UDF could do ?



- Ronald K.

 

[kittronald]

Forgot to add the purpose of this problem.

I'm trying to search the contents of a cell's formula and replace
the function name.



- Ronald K.

 

[Gord]

What do you want to do with SUM when you find it?

Edit>Find will find cells with SUM in formulas.

Edit>Replace will allow you to replace SUM with PRODUCT or any
function name in a formula or formulas.

The SEARCH or FIND function will only work with the results of
formulas, as you surmised.

Is this a follow-up to your previous question about a variable formula
based upon the text string in B1?


Gord Dibben Microsoft Excel MVP

 

[Gord]

Edit>Replace


Gord

 

[isabelle]

hi,

x = Application.Search("SUM", Range("A1").Formula)

 

[kittronald]

Gord,

Yes, I'm taking a different tack on globally changing a function.

I've used SEARCH so many times, I forgot I could record a macro
using Home\Find & Select\Find or Replace within a formula.

Of course, I realized that shortly after pressing the Send button
on the second post of this thread.

Thanks for your attention.


- Ronald K.

 

[kittronald]

Isabelle,

That's close to what I was trying to accomplish.

How could I shorten the macro below to select Sheet1, replace the
text "Sum" with "Product" in the formula only in the named cell on
that worksheet called "First_Cell" ?

Sub Test()
'
' Test Macro
'

'
Sheets("Sheet1").Select
Application.Goto Reference:="First_Cell"
ActiveCell.Replace What:="Sum", Replacement:="Product",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Cells.Find(What:="Sum", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub


I need to find a good book on writing macros in Excel.


- Ronald K.

 

[isabelle]

hi Ronald,

Sub Macro1()
x = Sheets("Sheet1").Range("First_Cell").Formula
y = Application.Substitute(x, "PRODUCT", "SUM")
Sheets("Sheet1").Range("First_Cell").Formula = y
End Sub

Sub Macro2()
x = Sheets("Sheet1").Range("First_Cell").Formula
y = Application.Substitute(x, "SUM", "PRODUCT")
Sheets("Sheet1").Range("First_Cell").Formula = y
End Sub

 

[kittronald]

Isabelle,

I added two names to the macro.


Sub Macro1()
x = Sheets("Sheet1").Range("First_Cell").Formula
y = Application.Substitute(x,
Sheets("Sheet1").Range("Current_Function"),
Sheets("Sheet1").Range("Selected_Function"))
Sheets("Sheet1").Range("First_Cell").Formula = y
End Sub


The name "Current_Function" comes from using the SEARCH function to
find the current function in "First_Cell".

The name "Selected_Function" changes based on selecting a value
from a ComboBox.

It works, but for brevity, do I really need to write
"Sheets("Sheet1").Range("NAME") for each argument ?


- Ronald K.

 

[kittronald]

Isabelle,

Regarding the name "Current_Function" mentioned above, I haven't
found a way to search for text in a cell's formula using a function.

Is this something that should be done with a UDF ?

For example, SearchFormula(find_text,within_text) where find_text
could be a named range.



- Ronald K.

 

[isabelle]

hi,

not because they're all cells named , but otherwise you can write

With Sheets("Sheet1")
x = .Range("First_Cell").Formula
y = Application.Substitute(x, .Range("Current_Function"), .Range("Selected_Function"))
.Range("First_Cell").Formula = y
End With

 

[kittronald]

Isabelle,

Thanks ... that works and is a lot easier to read !

I'm stuck on creating a UDF that will search a cell's formula for
specific text.

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

For example. ...

If A1 contained =VLOOKUP("Two",$A:$C,3,FALSE)

and D1=One, D2=Two, D3=Three

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

The formula would look like ...

=SearchFormula(Numbers,A1) and would return the matched value
- in this case "Two".



- Ronald K.

 

[isabelle]

hi,

i'm not sure if my understanding is correct for your request,

Sub test()
MsgBox SearchFormula(Range("A1"))
End Sub

Function SearchFormula(rng As Range) As String
x = Split(rng.Formula, Chr(34))
SearchFormula = x(1)
End Function

 

[kittronald]

Isabelle,

Thank you for all your help.

Can you recommend a book for learning how to write UDFs and Excel
macros ?



- Ronald K.

 

[isabelle]

hi,

there are many, depending what version you use,
follow this link

http://spreadsheetpage.com/index.php/books
http://www.amazon.com/s/ref=ntt_at_...arch-alias=books&field-author=John Walkenbach

 

[kittronald]

Isabelle,

OK, it figures that as soon as I declare something working ... it
stops working.

When I run the macro, I get the following error:

Run-time error '1004'

Application-defined or object defined error

When I click on the Debug button, the Visual Basic editor
highlights the following line:

y =
Application.Substitute(x, .Range("Current_Function"), .Range("Selected_Function"))

Any ideas ?

- Ronald K.

 

[isabelle]

hi,

on which excel version are you working ?
can you show your code ?

 

[kittronald]

Isabelle,

I'm using Excel 2007 SP2.

Sub Macro_Change_Function()
'
' Macro_Change_Function Macro
'

'
With Sheets("Data")
x = .Range("First_Data_Cell").Formula
y =
Application.Substitute(x, .Range("Current_Function"), .Range("Selected_Function"))
.Range("First_Data_Cell").Formula = y
End With
Application.Goto Reference:="First_Data_Cell"
Range(Selection, Selection.End(xlToRight)).Select
Selection.FillRight
Selection.SpecialCells(xlCellTypeLastCell).Select
Selection.FillDown
End Sub

Changes I made:

1) Changed the worksheet name from Sheet1 to Data

2) Changed the name "First_Cell" to "First_Data_Cell"

3) Added VBA code for filling right and down



- Ronald K

 

[isabelle]

did you put on one line ?


y = Application.Substitute(x, .Range("Current_Function"), .Range("Selected_Function"))

 

[kittronald]

Isabelle,

Yes, but the line got wrapped when I pasted it in the posting
above.



- Ronald K.