Hey everyone,
I'm using Excel 2007 and I'm trying to make things a bit easier on my coworkers (and really I'm just an intern, so I'm still new to Office 07).
We have a file I'll call "Expected Emergence 2008 4.xlsm" with a lot of cells that refer to other files, ie:
='O:\2008 4 LRC Studies\[AMC Petroleum 2008 4.xlsm]9 AL Net'!D54
Among other files. The one thing the file names have in common, however, is the "2008 4".
What I'd like to know is if there is a way to automatically change all of those references to our "2009 1" files instead of our "2008 4" files. I would want this to happen when a certain cell in the "Expected Emergence 2009 1" file is changed from "2008 4" to "2009 1". Is there a way to make this work? Thanks in advance, and sorry if my description is no good.
Last edited by jaymanre; 06-30-2009 at 11:32 AM.
Edit > Find & Replace, Replace 'O:\2008 4 with 'O:\2009 1, look in Formulas
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks for the reply shg.
Sorry I didn't elaborate enough. There are some references to "2008 4" that I do not want to change. I realize find and replace would work for the others, but there are a ton of references in the file to the "2008 4" group of files, so manually choosing which ones to change would be a pain. But thanks.
Any other suggestions?
Search/Replace can be run on a large selection, too. Perhaps it is all the references in one column?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JBeaucaire,
Three columns need to be changed per sheet, but there are many sheets. But I suppose that would be the way I should do it if there's no easier way. Thanks
Otherwise I was just going to use the "edit links" function in the data tab (or whatever it's called).
But really I'm just trying to convert 15 minutes of file updating to 5 seconds, if at all possible. If there isn't any way to do that it wouldn't be a huge deal...I just didn't have much to do at work yesterday, so I thought of doing something about this issue.
If it's the SAME columns that need the search/replace done, then record a macro of you doing it on one page. Then insert the "guts" of that macro into this code and it would then run the same code on every sheet in a workbook.
Sub SearchReplaceSpecific() Dim ws as Worksheet For each ws in Worksheets ws.Activate 'your search/replace code here Next ws End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
They are the same columns for each sheet. I'll check that out on Monday then (don't have 2007 myself), but it looks like it would work well (although I've never used a macro...). Thanks a lot!
what about
=INDIRECT("'E:\["&C1&".xls]martin'!$A$1") where c1 holds the workbook name
note other workbook needs to be open for link to update
or better still with the morefunc addin installed and activated
(get it from here http://xcell05.free.fr/morefunc/english/)
=INDIRECT.EXT("'E:\["&C1&".xls]martin'!$A$1")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
martindwilson,
Well, the INDIRECT formula did exactly what I wanted, except for needing the other workbook open. Is there any way to use this method without having that problem?
JBeaucaire,
For some reason, this site wouldn't open anymore after I tried the INDIRECT method, so I wasn't able to try the macroMaybe tomorrow.
After you've recorded you doing the search/replace once yourself, post the resulting code here and we'll help you merge that properly into the macro I suggested. Recorded code always can use with some streamlining and cleaning up.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Ok, I ran the macro, and here's the code I got (along with yours JB)
It's been a while since I've coded anything (and it was really basic stuff anyway), so I know something's up with it.Sub SearchReplaceSpecific() Dim ws As Worksheet For Each ws In Worksheets ws.Activate Sub MacroTest() ' ' MacroTest Macro ' Attempting to convert 2008 3 files to 2008 4. ' ' Range("B5:B34").Select Selection.Replace What:="2008 3", Replacement:="2008 4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Next ws End Sub
I'm getting two errors:
Expected End Sub, apparently for ws
and
For control variable already in use, at For Each ws In
Any tips?
Yea, I said paste in the "guts" of the macro, and you pasted in the whole thing, including the header/footer. Oops.
Sub SearchReplaceSpecific() Dim ws As Worksheet For Each ws In Worksheets ws.Activate Range("B5:B34").Replace What:="2008 3", Replacement:="2008 4", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False Next ws End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Ah, yeah that makes more sense. And it worked perfectly. Thanks for all the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks