search and replace formula substring

[parahumanoid]

Hi.

Is there a way I can find fields whose formulas contain a substring term_1 and replace this substring with term_2?
For example, formula is ='[Book1.xlsx]Sheet1'!A1 and there are many such formulas that contain a substring Sheet1 (not necessarily in Book1.xlsx, there may be references to other books, but with same sheet name). What I want is to find all occurrences of Sheet1 and replace them with Sheet43, so the resulting formula is ='[Book1.xlsx]Sheet43'!A1

Whether it's macros based or manual search and replace doesn't matter to me. I can see that the regular search works on formulas, but the replace part of it is something I don't know how to handle.

Any ideas, gurus?
Thanks a bunch.

Alex.

[parahumanoid]

OK, I got how to do it manually, maybe someone else needs this:

With cursor anywhere on the sheet and the first tab selected (General or whatever; I have a non-English version of Excel), use the Find and Select button to unfold the submenu and click Formulas. This will select all formulas on the active sheet. Now you can press Ctrl+H to call the Find and Replace dialog and type in the original and replacement substrings and go from there. For whatever reason, this doesn't work if you simply run Ctrl+H without first selecting the formulas.

The VBA sample to handle such functionality is still welcome.
Thanks for staying with me on this.

[Bob Phillips]

I don't have to do Find & Select> formulas first!