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

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  
Old July 27th 11, 06:58 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 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 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 09:56 AM.

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