VBA replace cell strings

[danielwen]

Hi. My first time posting. I require help with a VBA macro.

I have Range names in a workbook that I refer to in formulas in excel cells. One of the range names is “production”. The other range name is “production.cubic.feet”.

I want to add a suffix ( _copy) to these names using some kind of universal find and replace (because I have lots of these).

For example:

=production*2 change to =production_copy*2

=production.cubic.feet*6 change to =production.cubic.feet_copy*6

The suffix always needs to go at the end.

The issue is that when I do a universal replace of “production” with “production_copy” (which works for the first reference), “production.cubic.feet” unintentionally becomes “production_copy.cubic.feet” because it contains the string “production” and the suffix is then no longer at the end. Because this name does not exist I get a #name? error.

How can I add a suffix to the in cell name reference “production” without impacting other cells that include the string “production” as part of longer name references?

Any help will be greatly appreciated! I would like to automate this procedure as I need to do it on many occasions.

Thanks!

Daniel

 

[FormR]

Hi, welcome to posting!

Can you maybe search for "production*" and replace with "production_copy*" instead?

 

[danielwen]

Hi, thanks for the quick response. I want to do this without changing the range names as they are based on a strict naming convention. So unfortunately because I can’t make them unique I don’t this that would work. Any other ideas?

 

[FormR]

unfortunately because I can’t make them unique I don’t this that would work. Any other ideas?

Hi, I don't think you understood my suggestion, but it was flawed anyway

For example:
=production*2 change to =production_copy*2

If this is typical of your formulas - you can include the * in the find text. What I forgot though, is to do that we need to escape it's wildcard meaning.

So find: "production~*" replace with "production_copy~*" would alter the formula above.

 

[danielwen]

Hi, I understand what you meant now. Actually the multiplier operative was just an example. It could be followed by any math operative or nothing after the name. So it seams like this might just not possible...

 

[FormR]

it seams like this might just not possible...

Hi, it might be possible.

- do the "new" named ranges all end with "_copy" or was that just an example?
- does every named range that ends with "_copy" have a corresponding named range that doesn't?
- is it just on one sheet where the formulas need updating, or all sheets or some sort of subset?

 

[danielwen]

Hi, in answer to your questions in order: I) yes, II) yes, and III) no 1 sheet only where formulas need updating.

Can I send you a small example spreadsheet which will show exactly what I am trying to achieve?

 

[danielwen]

Ok, I’ve solved this. I just needed to first loop through all the range names first and change them give them a unique identifier like “production999”. This then changes the reference text in the formulas from “production” to “production999” and I can now replace this with “production_copy” without disrupting other formulas because it is unique. I then can change the range names back to remove the 999 afterwards.

Thanks for your help.