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)

--
isabelle

[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

--
isabelle

[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

--
isabelle

[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

--
isabelle

[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_ep_srch?_encoding=UTF8&sort=relevancerank&search-alias=books&field-author=John%20Walkenbach

--
isabelle

[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 ?

--
isabelle

[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"))

--
isabelle

[kittronald]

Isabelle,

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

- Ronald K.

[isabelle]

hi Ronald,

ok, i had tried to reproduce the bug but i was not able,
i prepared a littel file. can you tell me if there the same problem

http://cjoint.com/?AGBdlOkiKEu

--
isabelle

[kittronald]

Isabelle,

Thank you for going the extra distance to create your example
worksheet.

I downloaded your file and I think I see the problem in my
workbook.

With my limited development skills, I deduced the problem as being
caused by my workbook using two worksheets.

The cells for the names "Current_Function" and "Selected_Function"
exist on a worksheet called "Settings" as does the button that's
associated with the macro.

The name "First_Data_Cell" exists on the worksheet called "Data".

With the macro you created, it appears the With Sheets("Data") line
sets a default for everything between With and End With.

By prefixing the "Current_Function" and "Selected_Function" names
with "Sheets("Settings")", the macro works.

Below is the functioning macro:

Sub Macro_Change_Function()
'
' Macro_Change_Function Macro
'

'
With Sheets("Data")
x = .Range("First_Data_Cell").Formula
y = Application.Substitute(x,

Sheets("Settings").Range("Current_Function"),
Sheets("Settings").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

Range(Selection,
Selection.SpecialCells(xlCellTypeLastCell)).Select
Selection.FillDown
End Sub

Thanks for hanging in there.

For the time being, ... it works (fingers and toes crossed) :b

- Ronald K.