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.