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.

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.

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

EditFind will find cells with SUM in formulas.

EditReplace 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




On Thu, 21 Jul 2011 18:36:55 -0700 (PDT), kittronald
wrote:

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.

EditReplace


Gord

On Thu, 21 Jul 2011 18:41:30 -0700 (PDT), kittronald
wrote:

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.

hi,

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

--
isabelle


Le 2011-07-21 21:36, kittronald a écrit :
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.

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.

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.

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

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.

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.

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


Le 2011-07-23 19:18, kittronald a écrit :

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

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.

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

Isabelle,

Thank you for all your help.

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



- Ronald K.

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_e...n%20Walkenbach

--
isabelle

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.

hi,

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

--
isabelle


Le 2011-07-26 16:57, kittronald a écrit :
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,

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

did you put on one line ?


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


--
isabelle

Isabelle,

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



- Ronald K.

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



Le 2011-07-26 19:43, kittronald a écrit :
Isabelle,

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



- Ronald K.

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.