Do you have to have the replacement in the form, can we do it in code

Worksheets("Material").Range("B4").FormulaArray = _
"=SUM(" &
Replace(Replace(Replace(Replace(Replace(Replace(Re place(Range("A4").Value, "
", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") &
"*Unit_Cost)"


--
__________________________________
HTH

Bob

"Goody" wrote in message
...
I have a column of labels containing spaces, hyphens, parentheses, etc.,
that
Excel automatically converts to underscores when I use the labels as range
names. I am using a string of SUBSTITUTE commands ina a macro to build an
array formula that coverts the labels to range names. The macro functions
as
intended, except the resulting range name is always in quotes (e.g.,
"Test_Number"). The array formula then returns #VALUE!, because it does
not
recognize the range. How can I remove the quotes?

The macro line is shown below:
Worksheets("Material").Range("AB4").FormulaArray =
"=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,""
"",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"")
* Unit_Cost)"

Thanks