Find and Replace File References With VBA

Hi, I have many large closed workbooks I am trying to reference using direct references. I do not want to open them all and use the INDIRECT function. Also the =PULL function is not working for me and there are probably too many for this to be very efficient.

So I just want to write a macro to change (find & replace) the references based on the values in first column.

So this….

Filename____Reference
1001 _______=+'[1001.xls]Sheet1'!$a$1
1002 _______=+'[1001.xls]Sheet1'!$a$1
1003 _______=+'[1001.xls]Sheet1'!$a$1

Would become this….

Filename ____Reference
1001 _______=+'[1001.xls]Sheet1'!$a$1
1002 _______=+'[1002.xls]Sheet1'!$a$1
1003 _______=+'[1003.xls]Sheet1'!$a$1

There are hundreds of rows with many references so I would like a VBA script to find and replace.

Any help would be GREATLY appreciated. Thanks!

Mark xpost http://www.mrexcel.com/forum/excel-q...lications.html

Here's a simplified example, it's just a macro to find a placeholder value in a row ("1001" in this case) and replace with the value found in the first column (starting in row 3). So row 3 would find the value "1001" and replace all instances in the row with "1002." Row 4 would also find "1001" but replace with "1003." I've also attached a workbook with Before and After worksheet examples.

The real task is replacing file name references to closed workbooks, but hopefully this clarifies the logic which seems pretty simple but I can't make it work with VBA.

Please help if you can, thanks!!

Before:
Filename Ref1 Ref2 Ref3
1001 1001 1001 1001
1002 1001 1001 1001
1003 1001 1001 1001
1004 1001 1001 1001
1005 1001 1001 1001
1006 1001 1001 1001
1007 1001 1001 1001

After
Filename Ref1 Ref2 Ref3
1001 1001 1001 1001
1002 1002 1002 1002
1003 1003 1003 1003
1004 1004 1004 1004
1005 1005 1005 1005
1006 1006 1006 1006
1007 1007 1007 1007

File References Example.xlsm