I'm trying to create a macro that will modify the Refers to: field of a
defined name by doing the following:

1) Create a UDF called "SEARCHNAME" that searches the Refers to: field
of a defined name and returns a matched value.

For example, =SEARCHNAME(find_text As Range, within_text As Range)
where find_text is a named range containing text values and within_text is
the Refers to: field of a defined name.

2) Set value "a" = to the Refers to: field of the name "Test_Formula"
which contains =SUM(1,2).

3) Using the function SEARCHNAME, iterate through the values in the name
"Functions" and see if they occur in value "a" and set the value "b" as the
found value.

The name "Functions" refers to the range A1:A2 where A1 contains
the text SUM and A2 contains the text PRODUCT.

4) Set value "c" = to the selected item in ComboBox1 that contains the
text values SUM and PRODUCT.

5) Modify the Refers to: field in the name "Test_Formula" by replacing
value "b" with "c"


I'm not a developer, but I was thinking along the lines of the
following:

Sub Macro_Modify_Name

a = ActiveWorkbook.Names("Test_Formula").RefersToRange

b = SEARCHNAME("Functions","Test_Formula")

c = selected value of ComboBox1

d = Application.Substitute(a,b,c)

ActiveWorkbook.Names("Test_Formula").RefersToRange = d

End Sub


Any ideas ?


- Ronald K.