Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old July 24th 11, 02:03 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,

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  
Old July 25th 11, 12:49 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,

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  
Old July 25th 11, 01:38 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,

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  
Old July 25th 11, 05:31 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,

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  
Old July 25th 11, 03:10 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,

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  
Old July 26th 11, 09:57 PM 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,

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  
Old July 26th 11, 10:36 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,

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  
Old July 26th 11, 11:24 PM 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'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  
Old July 27th 11, 12:33 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

did you put on one line ?


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


--
isabelle

  #20   Report Post  
Old July 27th 11, 12:43 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,

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



- 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 08:42 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