Find and Replace in VBA does not work

[pitinswiss]

Hi,

In a userform I created a listBox, added items to it and then a macro, depending on the value I will select, should make replacements. I recorded the macro Find/replace (the msgBox works after the replacements. Means it jumps over the code).

Here is the beginning of the macro (is a If/elseif after):

Please Login or Register  to view this content.

[Norie]

Are you find/replacing within formulas?

[pitinswiss]

Hi Norie,

Yes it is to replace inside formulas

pit

[Norie]

Does this make a difference?

Please Login or Register  to view this content.

[pitinswiss]

Unfortunately it does not change,

pit

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pitinswiss]

I am getting closer to the problem. I created a loop to see how the Find and Replace behave once VBAed

I spotted what Excel "doesn't like". It is actually the chain of characters to find and replace into the formulas.

I highlighted in red the problem. If I change the characters in red by just "APAC" eg, it will work fine...

I did not figure out still how to resolve the issue

Please Login or Register  to view this content.

[Norie]

Your original code, with the minor changes I suggested, worked for me just fine.

If you just want to replace LATAM with APAC just use this.

Please Login or Register  to view this content.

That works for me.

[pitinswiss]

With what is in red above, will not work. This is my issue. I don't want to change just a word. I need to change the piece of formula.

[Norie]

Strange, what I posted does change formulas.


The only difference between this, "$R:$R;""LATAM""", and this, "$R:$R;""APAC""", is APAC.

[pitinswiss]

Please refer to the initial post (which is still only an extract). I need to update the columns as well in the formulas. This excel workbook will deal with all regions over the world.

I would still value the answer as to how to update the part of the formula in red characters above

[Norie]

It really would help to see some sample data.

I think the problem could be something to do with the quotes you are using.

[pitinswiss]

This is a typical formula in the cell for which I would like the code to work:

In red is the part I want to change (same for column R and column Q).

Please Login or Register  to view this content.

[Norie]

What should it be replaced with?

[pitinswiss]

for example:

$R:$R;"LATAM"

[pitinswiss]

Hi Norie, everyone,

Norie, thank you so far for your time spent trying to solve the issue. I thought maybe it could help to seek further heads

Would there be someone who could resolve this? Or even help finding a way to trick Excel.

Thank you
pit