Home 
Search 
Today's Posts 
#11




Searching for text within formula
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 20110723 19:18, kittronald a écrit : It works, but for brevity, do I really need to write "Sheets("Sheet1").Range("NAME") for each argument ? 
#12




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




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




Searching for text within formula
Isabelle,
Thank you for all your help. Can you recommend a book for learning how to write UDFs and Excel macros ?  Ronald K. 
#15




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




Searching for text within formula
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: Runtime error '1004' Applicationdefined 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. 
#17




Searching for text within formula
hi,
on which excel version are you working ? can you show your code ?  isabelle Le 20110726 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: Runtime error '1004' Applicationdefined 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. 
#18




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




Searching for text within formula
did you put on one line ?
y = Application.Substitute(x, .Range("Current_Function"), .Range("Selected_Function"))  isabelle 
#20




Searching for text within formula
Isabelle,
Yes, but the line got wrapped when I pasted it in the posting above.  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) 