Find and replce takes 5-6 hours

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
54
Like any software developer, when I make significant changes to my spreadsheet I increment the version (So Ref Stats V7 becomes Ref Stats V8. I then have to change the references in one sheet for a couple of different files. Each file takes up to 6 hours to replace the references. (There's about 15,000 references to be updated and that grows by about 2,500 per year).

I have a HEDT system running 32GB of RAM with a way overclocked CPU and 970 Pro SSD and I still can't get the time down.

Is there a way to reduce the time it takes to update the references without "Don't rename the file"?

thanks
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,372
Office Version
2013
Platform
Windows
How did you do it?
If using macro the can you show us the code?
 

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
54
Ctrl-F, replace, put in "find what" and "replace with" set options to Within: Sheet, Look in: Formulas, click Replace All.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,372
Office Version
2013
Platform
Windows
Well, you'll need a macro to do that.
Can you put the list somewhere in 2 column contains:
in first column: the existing string/reference to be replaced.
in second column: the new string/reference.

Also can you post some examples of the formula & also the expected result?
 
Last edited:

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,708
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
Have you tried disabling excel auto calculation do the find replace and enable calculations again
 

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
54
Slight miscalculation on the number of references replaced... ~41,000

Auto calculate is always turned off.

If I click the excel icon on the taskbar Excel crashes. If I click on the Excel display Excel crashes. It's just a matter of starting the replace and waiting for it to finish.

Formulas are many and varied but they are links to another spreadsheet '\\server\tanya\basketball\ref stats v7.xlsx.

One cell could have 8 references to that spreadsheet. After the replace, the V7 becomes V8, and next time it will become V9 and so on. The sheet being updated has 1840 rows and 300 columns. The work book has 10 sheets and several of them have references to be replaced. The total time for all sheets except the 'summary' sheet (the big one), is 15 minutes.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
796
Office Version
2007
Platform
Windows
Like any software developer, when I make significant changes to my spreadsheet I increment the version (So Ref Stats V7 becomes Ref Stats V8
Hi Yanta. Like you I am a software developer and fully appreciate the need for version control. However, can't you get round the issue by always having the latest version called Ref Stat Current, and taking backup copies of each previous version?
 

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
54
Yes, that would seem to be the only solution.
And embed some version metadata in the spreadsheet - as there are always minor revisions (v7.1.1, v7.2.0 and so on).

My original concern was that something was "broken" in excel that was causing the extended times. Perhaps that's just how it is.

Gosh, imagine doing this 5 years ago on a mainstream computer... Would take days :)
 
Last edited:

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,708
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
Excel being smarter than is sometimes required is probably trying to evaluate and qualify each link and establish that link hence the delay so version control outside of excel makes sense
 

Forum statistics

Threads
1,082,295
Messages
5,364,362
Members
400,792
Latest member
Dxmiian

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top