Find and Replace text within string

**Soulbringer** · 06-19-2012, 11:58 AM

Hi,

I'm new to this forum and only starting out with VBA in excel and I am wondering if anyone here can help me.

I am looking for a bit of code to search an external link e.g. U:/Workbooks/[Example123456.xls]Sheet1'!$E$10
and replace the 123456 only with a cell ref. for example:

if
A2 = 654321

then
link=U:/Workbooks/[Example654321.xls]Sheet1'!$E$10

if
A2 = 555555

then
link=U:/Workbooks/[Example555555.xls]Sheet1'!$E$10

the numbers are always 6 chars long and I need it to do this across serveral sheets

A1=Sheet1 A1:O40
A2=Sheet2 A1:O40

and so on.
This is what I have currently

Please Login or Register  to view this content.

Many thanks in advance

Graham

**Leith Ross** · 06-19-2012, 01:34 PM

Hello Soulbringer,

Welcome to the Fourm!

I have a few questions about your post.

Is the link part of the cell's formula?
Are all worksheets to be searched?
Are the file names just 6 digits followed by the extension like 123456.xls?

**Soulbringer** · 06-19-2012, 03:41 PM

Hi Leith,

Thanks for the quick reply,

In answer to your questions no the link is not part of the cell formula, I will try and explain what results I am looking for.

I have designed a workbook that will show me financial data from 25 projects, the sheets in my workbook are exact replicas of the finance sheet, at the end of each month I have to report on the financial position of these projects. To prevent me having to open each workbook and print the pages I created one workbook, however these projects are subject to change, new ones will open and old ones will close, but 25 seems to be the consistant number.

Each Project is assigned a six digit number I would like to enter these numbers in to cells on a sheet which then in turns updates all the links in the workbook, including the sheet names if that is possible.

I have attached an example layout.

**Leith Ross** · 06-19-2012, 04:06 PM

Hello Soulbringer,

Each project in the workbook is a separate worksheet and the list that reflects which sheets to examine is on the "Data" worksheet. Have I got that right?

**Soulbringer** · 06-19-2012, 04:14 PM

Hi Leith,

That is correct. I would like the links in the workbook to be changed according to the project numbers on the Data sheet.

**Leith Ross** · 06-19-2012, 11:30 PM

Hello Soulbringer,

Sorry for the delay. The attached workbook has been modified from your original post. A dynamic named range "Project_Number" now refers to the project numbers from A2 down to the last project number in column "A". A button has been added to the "Data" sheet to run the macro.

Please Login or Register  to view this content.

**Soulbringer** · 06-20-2012, 07:12 AM

Leith,

Thank you for that code.

I have altered it slightly to incorperated it into the actual workbook I am using.
I can not get all the work books to update though.

I cannot however upload the file from work as we use sensitive material.

I shall try my best to explain what I have done.

Here is the code.

Please Login or Register  to view this content.

In Column H is the data that I would like to place within the string
In column D is the worksheet name to be looked up

The macro doesn't need to change the worksheet names as I have another macro working indepentantly

Many Thanks

Graham