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.
