Home 
Search 
Today's Posts 
#1




Searching for text within formula
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. 
#2




Searching for text within formula
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. 
#3




Searching for text within formula
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 followup 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. 
#4




Searching for text within formula
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. 
#5




Searching for text within formula
hi,
x = Application.Search("SUM", Range("A1").Formula)  isabelle Le 20110721 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. 
#6




Searching for text within formula
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. 
#7




Searching for text within formula
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. 
#8




Searching for text within formula
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 
#9




Searching for text within formula
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. 
#10




Searching for text within formula
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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Searching for text  Excel Worksheet Functions  
Formula for searching for a text string  Excel Discussion (Misc queries)  
Searching for text  Excel Discussion (Misc queries)  
help searching text  Excel Discussion (Misc queries)  
Searching for text in cells  Excel Discussion (Misc queries) 