Removing Quotes from Range Names


G

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
 
Ad

Advertisements

J

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
 
B

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)"
 
G

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
 
B

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?
 
G

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
 
Ad

Advertisements

Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top