Find and Replace part of another formula with value of cell

[johnnyjoe1076]

I have a formula in cell J2:

='H:\Folder[CI10001G.xlsm]Sheet'!$C$5

I need a macro that will change the part of the formula that is "CI10001G" to the value of cell A2 which would basically be something along the lines of "CI10004D" so the formula would change to the following:

='H:\Folder[CI10004D.xlsm]Sheet'!$C$5

And then this formula will then successfully look in the newly directly file path. Any help would be extremely appreciated!

Thanks,
Johnl

 

[pplstuff]

If this formula is all through j:j:

Highlight the column - hit crtl +f - replace -
replace
1G.
with
4D.

Edit**** sorry, didn't realize your folder might be different in different cases... this might not be the best solution.

 

[mathchick]

fyi ctrl+h will bring up the find & replace with the replace tab selected

 

[johnnyjoe1076]

Allow me to explain further. It is not that simple. This is a multi line database that contains many different line items. I have a macro that already drops in new data from another file when it has been approved. I then add it to this file and the last step I am trying to accomplish is having the "J" cells reference an important piece of data from a 3rd file that is specific to only that line. The formula can only be changed via macro if I can find a way for it to replace the 10001G with the value of cell "A". If I tell the macro to replace with specifics it will not work because each addition will be different. I hope this makes sense.

 

[pplstuff]

You could do:

k2: =replace(j2,find("[",j2),8,a2)

this assumes that the text between [ and ] in column J is always 8 characters. It can be adapted if that is not the case. (might need to do find()+1)

 

[johnnyjoe1076]

I tried that exactly as you typed it, but kept getting a #value! ERROR. I assume I am getting that because it can't find what I am looking for in the "formula" of the cell but it is rather only looking at the "value". I dont know what I am doing wrong then.

 

[Jonmo1]

Why not just go to Edit - Links - Change Source.

You can even record a macro while you do it to get some syntax.

 

[pplstuff]

I tried that exactly as you typed it, but kept getting a #value! ERROR. I assume I am getting that because it can't find what I am looking for in the "formula" of the cell but it is rather only looking at the "value". I dont know what I am doing wrong then.

You could try to replace "=" with "a=" with ctrl+f, this will make it so J:J doesn't return a value. Then use what I suggested. at the end, replace back "a=" to "="...

Hope that makes sense! Good luck.

 

[pplstuff]

Why not just go to Edit - Links - Change Source.

You can even record a macro while you do it to get some syntax.

Because he might have 100 sources!

 

[johnnyjoe1076]

You could try to replace "=" with "a=" with ctrl+f, this will make it so J:J doesn't return a value. Then use what I suggested. at the end, replace back "a=" to "="...

Hope that makes sense! Good luck.

I think this is exactly the answe I need to make this work, however I apologize I am not quite following your instructions as to where/how I make the suggested changes. Could I trouble you to rephrase what you are suggesting?