Count and Then Replace Occurrences in Formulas with VBA

[muze]

I have a master spreadsheet with ~15,000 links to about 20 external files.

The 20 external files are generated through another macro process and when they are created have a new version number appended (e.g. 04.12). In the master spreadsheet, I need to edit and replace the version number each each time the support files are updated. So for example, replace 04.12 with 04.13. What I would like to do is two things:

1) Count the occurrences of the version number
2) Replace the version number based on a value.

I am currently doing the replacement with a recorded macro and this code:

Please Login or Register  to view this content.

What I can't figure out is how can I count all of the cells where the formula contains the version number (e.g. 04.12) and then loop through those and keep a counter in the status bar so I can see how far along it is. The code above works, but I recently just upgraded to 2007 and this process now takes so long, I want to be able to show progress at the bottom to the users.

[mike7952]

What column are you trying to update the version in and what row does your data start on? Can you upload a cutdown copy of your wb

[muze]

The cells start in column Z and goes to AI and the rows start in 326 and goes to 3010. The data is not, however, all together, so there's spaces in the between the rows when there's major changes in the data sources. I don't think that would matter, though.

I can try to upload part of the file, but will need to cut it down quite a bit.

[mike7952]

So the Version can be in Column Z to AI?

[muze]

Yes, the formulas with the version numbers to be replaced are in in col Z to col AI.

[mike7952]

When you say Formulas, are they formulas o are they Values/Text?

Please Login or Register  to view this content.

[muze]

They are formulas. So for example, here's what is in row 326:

Please Login or Register  to view this content.

So in the above, I'm looking to count all the cells were a 04.12 exists and then ultimately replace it with 04.13.

[FDibbins]

have you considered using indirect() and putting the new version in a specific cell somewhere? that way, you would see which version you were working with last, and just by changing the cell, the links would update?

hmm om 2nd thoughts, indirect requires the files to be open for them to update/calc

[mike7952]

Ive tried but having some trouble. How about turning Calculation mode off at the start of your code and turn it back on at the end. Then the status bar should show a Calculation percentage

Please Login or Register  to view this content.