Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 22nd 11, 02:36 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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  
Old July 22nd 11, 02:41 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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  
Old July 22nd 11, 02:53 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
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  
Old July 22nd 11, 04:03 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
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  
Old July 22nd 11, 02:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
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  
Old July 23rd 11, 12:08 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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  
Old July 23rd 11, 12:24 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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  
Old July 23rd 11, 03:11 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
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  
Old July 24th 11, 12:18 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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  
Old July 24th 11, 01:47 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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.


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 09: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 03:16 AM


All times are GMT +1. The time now is 10:11 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017