Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 86
Default 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 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 86
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Searching for text within formula

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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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.


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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 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 ?


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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 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.
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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:

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.
  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Searching for text within formula

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.

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Searching for text within formula

did you put on one line ?


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


--
isabelle

  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default Searching for text within formula

Isabelle,

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



- Ronald K.


  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Searching for text within formula

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.

  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default Searching for text within formula

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching for text EBnLP01 Excel Worksheet Functions 2 July 14th 09 08:32 PM
Formula for searching for a text string KellyB Excel Discussion (Misc queries) 5 November 20th 06 10:20 PM
Searching for text strippier Excel Discussion (Misc queries) 2 May 23rd 06 09:08 PM
help searching text Nikko Excel Discussion (Misc queries) 2 April 28th 05 03:32 PM
Searching for text in cells Matt Excel Discussion (Misc queries) 1 January 31st 05 04:16 AM


All times are GMT +1. The time now is 11:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"