Help - Replace a string in a whole workbook inside formulas

Perhaps...

Please Login or Register  to view this content.

Is the string part of a link to another workbook? If so, it would likely be faster to alter the link source path, rather than all formulas individually.

Thank you, sintek and XLent! I don't know why both codes don't work, but I assume it is the way I do "the job".
I keep the macros in another workbook (xlsm). I open it...
Then I open also the target file (also xlsm that has its own macros) and run the macros stored in the first workbook inside the target.
I have many target files to do the change.

Am I doing wrong for keeping "my tools" inside other file to avoid messing up the target files?

I actually need to do 3 tasks on the target workbook:

1) Unprotect all worksheets with macro nr. 1
2) Replace a part of a formula inside few worksheets (like 7 worksheets)... with macro nr. 2.... but I think it is easier to tell it to replace in the whole workbook...
3) Protect all worksheets back with a password with macro nr. 3

I have the codes for tasks nr. 1 and 3 - and they do what it is supposed to do.
The task nr 2 is the one that does not work yet. Thank you rorya for asking... maybe this is the reason the codes do not work.

Originally Posted by Alexander2020

Thank you rorya for asking... maybe this is the reason the codes do not work.

Hard to say, as you haven't actually answered...

Originally Posted by rorya

Hard to say, as you haven't actually answered...

I didn't answer to your question cause I didn't understand it. As I explained in other post, I have one workbook with the macro that should make the change inside a formula located in other workbook. I do not want to add this macro inside the target workbook, just run it when both workbooks are opened (active). I proceed like that with many other macros, cause I do not want to add something inside the target workbooks (I have many workbooks that need the change and I send them to other people). If this is what it means that "the string is part of a link to another workbook"... then the answer is "Yes".

Meantime one friend sent me this code:

Please Login or Register  to view this content.

I should mention that I do not know how to adapt this code to my situation. The formula that need to be changed is located in the cell "C8" inside any of the 7 worksheets of my target workbook. And it goes down for many cells only in the column "C". The real formula that need to change looks like that (I didn't want to complicate the things before): =IF(B8<>"";VLOOKUP(B8;N_Servicii!$A$4:$K$5003;9;FALSE);"")
It looks inside a list with name of streets, while it should look at a list with name of cities. The difference is simple. I need to replace "9" with "11".
My first approach was to use a macro that search inside the whole target workbook for the string "Servicii!$A$4:$K$5003;9" and replace it with "Servicii!$A$4:$K$5003;11".

I was not able to make the macro from my friend work for me, but I was able to make myself a code that works, but instead of searching inside the whole workbook, it looks specifically at the location of the formulas that need to be changed. This is my code (of course, inspired by search on the net):

Please Login or Register  to view this content.

If you ask why I put this inside my code: "Worksheets("Alpha").Activate"... My code without it was working only if I was selecting the Alpha worksheet before running the macro. Why? Because before the 7 worksheets (my targets) I have other three worksheets... lets call them "Sheet1", "Sheet2" and "Sheet3" that has nothing to do with my problem. Excel by default selects the activated sheet to be "Sheet1". I do not know why or how... but telling that worksheet "Alpha" is the first to be activated, then all goes well. Otherwise it replaces wrong cells.
Also... about my code... I put the range of formulas to be changed between cells C8:C100. I never get so many cells to modify in the "C" column, but I didn't know how to tell Excel to do the modification only where is a formula inside the cell and stop when the cell is empty.
Of course I have no idea how to make the code to look better and I am certain in can be made shorter instead of repeating.

What is important for me is that I have solved my problem. If you know how to improve my code or my friend's code (that search inside the whole workbook) then I thank you in advance and try it.