Find and Replace Macro stops working

[randolphoralph]

I created a macro that updates formulas on different sheets for about 70 rows. For some reason the formula will run through about half of the rows and then runs through the rest of the rows really quickly and does not complete the find and replace.

I have found if I change the macro to do one sheet at a time I can manage to get about 3 sheets done before it does the same thing.

I have tried to rerun the macro after it stops completing the find and replace and it does not work. However if I save and close the workbook and reopen the workbook and play the macro then it will complete the find and replace on the rows it did not finish. However just doing a save does not work the workbook has to be closed and reopened.

It is almost like the macro is using all the memory....filling the clipboard...or Excel has a maximum on find and replace. I am at a complete loss as the macro worked last month when I ran it. To the best of my knowledge there has been no changes to Excel since last month.

Please Login or Register  to view this content.

[stnkynts]

Please Login or Register  to view this content.

Note: It would be faster if you could reduced the range. I.E. a specific cell rather than the whole row

[randolphoralph]

Could the macro look at just those 9 sheets?

[stnkynts]

Are the sheets actually named "1", "2", etc or are you trying to reference their index number?

[randolphoralph]

The sheets are named 1, 2, etc.

[stnkynts]

Please Login or Register  to view this content.

Note: Don't forget the finish filling out the arrays so that they cover all the parameter that you need.

[randolphoralph]

Thank you stnkynts. I used your code and finished filling in the arrays. Once I ran the macro it took about 8 minutes and completed 3 sheets and stopped on the 18th row on the 4th sheet. I tried to run the macro immediately after it stopped and the macto it took about 2 seconds for the macro to complete and nothing was completed. After saving the workbook and closing and reopening I was able to run the macro again and complete the rest of sheet 4, 5, and almost all of sheet 6 before it stopped.

I am at a complete loss. Maybe the only solution is to open and close the spreadsheet.

[stnkynts]

Submit your worksheet.