How can I speed up this find & replace VBA

[Jennasis]

Hi guys and gals! :-)

I really need to speed up this find & replace VBA, if at all possible, as it is currently taking 2 hours to complete.

The workbook has sheets named by day of the week and each sheet holds a row for each member of staff - these staff members each complete a time-sheet, which is then linked to the relevant day on the master performance workbook (which is what this macro updates).

The purpose of the macro is to update the workbook for a new week, replacing, for example, Week 12.13 with Week 13.13 in all links on all week day tabs.

It is not possible to have all time sheets/work books open, as some have the same name, plus there are that many of them that most NC/PCs here don't have the memory. I have attempted to compensate for this by making sure the performance tracker doesn't update when changing links, but it still takes 2 hours.

I have tried to use a PULL function, which was even slower - indirect is not possible, due to not being able to open all time sheets.

Any ideas much appreciated!

Please Login or Register  to view this content.

[tehneXus]

Hi,

instead of looping through all sheets and comparing their names you can check if the sheet exists and use it then, this will speed up your code.

As far as I understood from your description you want to replace sth. in more than one workbook but I cannot find this in the code.

Could you upload a sample master book and at least 2 books where the replace has to be done, this will save me a lot of guessing.

[Jennasis]

Sorry that I have not yet been able to upload some sample files - restrictions on uploads from the work place currently prevents this, though I am going to send them from home when I get the chance.

The macro is intended to find and replace the week in the daily tabs/sheets, for example, it will find the week currently populated, say "Week 13.13" and replace with the new week "Week 15.13", in all formulae on all daily sheets - there are currently approximately 50 populated rows and 30 columns on each sheet and each formula has 2 or 3 instances of the string, so apprx 4,500 instances to replace per sheet, making 31,500 instances to change in total over all sheets. This takes around 2 hours to complete with the current macro, on my work PC.

[tehneXus]

'Hi,

'this will loop the sheetnames in U4:U10 and, if the sheet exists, replace the value in V28 with the value in V22 for the range C10:AS98 in each sheet found. it runs 0,4 seconds to replace 37620 findings in this example file: Replace_2.0.xlsm
'To loop workbooks: Do you have a list of workbook paths?

Please Login or Register  to view this content.

[Jennasis]

Thanks for the response :-)

There is a workbook/timesheet for each adviser that the report looks at and each timesheet has 7 tabs, one for each day of the week (this can be reduced to one, expanded tab, if this would improve speed at all, though the issue does not appear to relate to the links as updating is switched off during the find + replace) - the file path is like Time Sheets\Week 15.13\Manager\Adviser.xls . There are 4 managers, each with approx 12 advisers - all the advisers only have access to Excel 2000 (due to license restrictions), which means the files are larger and more cumbersome than Excel 2007+ xlsx/xlsm and have to be saved in xls only.

It is only this master report that needs to be updated each week (to the new week) and only 7 sheets within it, named Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday have formulae that need to be altered. As the substructure to each week's folder is the same (manager then adviser.xls) it is only the week portion of the file path that needs changing.

I would usually use an indirect function or similar to find the data, based on the week being displayed in a cell value and use concatenation to build the file path, but this doesn't work in this case, due to the files needing to be open. The "Pull" function, in it's non-volatile form, is also not fit for purpose, so the only solution seems to be this find and replace macro...