Find and Replace part of another formula with value of cell

johnnyjoe1076

Board Regular
Joined
Jan 18, 2012
Messages
77
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
Why not just go to Edit - Links - Change Source.

You can even record a macro while you do it to get some syntax.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,212,141
Messages
6,106,197
Members
448,005
Latest member
Valvictor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top