Macro for Replacing Text Produces Run-Time Error 91

Hi! I have a macro that works fine and with no errors if there is a single instance of a string--" Total"--found within a cell in column D. It also works until the string can no longer be found, but once all the values of " Total" have been found and replaced, the macro stops and the following error pops up: "Run-time error '91': Object variable or With block variable not set." The debug option pops up at the point in the code highlighted in blue below, and is obviously related to not finding " Total" in column D.

I have searched and searched for code and keep trying to modify the macro to eliminate the error, but with no success. I'd like this this macro to run automatically on each worksheet as they are created rather than going to each worksheet and running the macro separately. Although this is a workaround that will produce the desired formatting of the worksheets, it is more time-consuming.

Any help you can offer will be greatly appreciated after spending MANY hours trying to fix this! I am not locked into any of the code other than that needed for the formatting.

Thanks in advance!

Please Login or Register  to view this content.

You need to take the same precaution as you did with the outer Find loop - i.e. test if the value is found by assigning it to a range object:

Please Login or Register  to view this content.

Untested, but perhaps something like this:

Please Login or Register  to view this content.

Glad to help.