How do I replace part of a formula using VBA?

[Vaslo]

I'm trying to replace a portion of a long link so that it can quickly link to identical workbooks with different values with different names.

The original link is:
='Y:\FY14 Strategic Plan\BU Templates\Consolidated and Parsed by Template\Initial Test\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]Brand Sales'!D12 (where D12 obviously changes)

The piece I'd like to change is:
"'Y:\FY14 Strategic Plan\BU Templates\Consolidated and Parsed by Template\Initial Test\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]"
to
"'Y:\FY14 Strategic Plan\BU Templates\North America\US Consumer\[Manual_Schedules_BU_Strat Plan_final USCP.xlsx]"

I tried to use this code:
ActiveSheet.Cells.Replace What:=strOld, Replacement:=strNew, _
LookAt:=xlPart, MatchCase:=False

but it doesn't work for formulas - it does work replace strings that don't relate to formulas.

Why do all this? Because I have to create 12-13 workbooks with around 30-40 tabs tied to various accounts, and each tab has between 20-100 references to this code. This is just the most efficient way I can think to do it.

Any help on this would be greatly appreciated, even if it is a new approach. again in short:
-Select a worksheet
-Find a particular part of a link
-replace it with a different part (which happens to be loaded as a string in an array)

Thanks.

[BuckoAk]

Give this a try, Pick the Cell you want to update and run the command.
Copy the code to the worksheet that needs updating.
It looks at the Row and applies the appropriate D##

You can also assign this to a button to speed things up.

Please Login or Register  to view this content.

[Vaslo]

BuckoAk,

This is an interesting solution, but it won't be very practical. I have a dozen workbooks with 30+ tabs with sometimes 100 links on each tab. Not only will this be tedious to click, it's stuck with "D" and I need all sorts of columns addressed. Thanks for the attempt though.

[BuckoAk]

Attempt? I'm just getting started.

Just an Fyi for the next time, give all necessary info like multiple columns and any other important info.
Your example was linked to "D", you know what you want but the person on the other end doesn't know this or sometimes has to guess at some things.

Alright this will tackle any multiple rows & columns, select all cell needing to be changed then run the command.
For example Select D1 thru D50 then run the command.

Please Login or Register  to view this content.