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

 

[johnnyjoe1076]

Never mind! The light switch went on 3 seconds after I clicked post! I got it now, thanks pplstuff! That will work!

 

[pplstuff]

Sure:
1) highlight J:J
2) ctrl+f
3) replace tab
4) replace = with a= <--don't need quotes
5) use the formula I suggested a few posts back k2: =replace(..,find(..))
6) highlight K:k
7) ctrl+f
... replace a= with =

I haven't tested this, and you may need to adjust the find replace a bit for your situation.

 

[johnnyjoe1076]

I tested it and it worked!, thanks again!