Removing Quotes from Range Names

[Goody]

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

 

[Jacob Skaria]

To remove double quotes use Chr(34) instead of """".

=Replace(strData,Chr(34),"_")

Would suggest to use VBA Replace function

If this post helps click Yes

 

[Bob Phillips]

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(Replace(Range("A4").Value, "
", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") &
"*Unit_Cost)"

 

[Goody]

Thanks, Bob. Your solution works great. I now have a new problem. When Excel
encounters a right parenthesis at the end of the label, it does not convert
it to an underscore when using the label as a range name. How can I duplicate
that it code, while still converting parentheses within the label?

Goody

 

[Bob Phillips]

I am not sure what you mean. I created a simple value of (help) and the code
converted that to _help_ as required.

What am I missing?

 

[Goody]

I use the formula that the code creates to sum cells in a large table. The
rows in the table were assigned names using the Insert, Name, Create command.
When Excel creates range names using this method, it does not convert a right
parenthesis at the end of the label to an underscore; but the code you helped
me with does. I assume I could use an IF test in the code to check the
position of the right parenthesis, and drop it if it is at the end of the
label. I was wondering if there were an easier way.

Goody

 

[Bob Phillips]

I would do that, test the last char for a ) and lose it.

 

[Goody]

I use the formula that the code creates to sum cells in a large table. The
rows in the table were assigned names using the Insert, Name, Create command.
When Excel creates range names using this method, it does not convert a right
parenthesis at the end of the label to an underscore; but the code you helped
me with does. I assume I could use an IF test in the code to check the
position of the right parenthesis, and drop it if it is at the end of the
label. I was wondering if there were an easier way.

Goody