Excel macro stops after Replace statement

[jean-marc]

Take the following simple macro:

Please Login or Register  to view this content.

The macro stops after the Selection.Replace [...] statement; it makes the replacements OK, but stays on range A1:C1 and never selects range A3. I cannot get the macro do to *anything* after the Selection.Replace task. The macro does not show any error; just stops.

I suspect this is because Excel would normally pop up an info dialog "Excel has completed its search and has made [n] replacements" (on which the user has to click OK), although I do not see this dialog when running the macro. Could this be causing the macro to freeze? Is there a way of suppressing this Replace info dialog from within the macro? I tried Application.DisplayAlerts = False - to no avail.

[Marcol]

Hi jean-marc

Welcome to the Forum....

Please wrap your code in code tags, before the moderators get you...

Click here - Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Cheers

[romperstomper]

Do you have any other code in the workbook/sheet that may be interfering, such as a Worksheet_SelectionChange event?
Incidentally, the selection is unnecessary:

Please Login or Register  to view this content.

performs the replace without selection.

[jean-marc]

Hi romperstomper,

Prompted by your hint of an interference, I closed down all other spreadsheets I had open (which also contained macros I had previously executed), and it now works fine! Will need to narrow down which is the culprit by trial and error. Any idea how to overcome this problem? Would you view this as a bug? Seems quite legitimate to have several spreadsheets open with previously executed macros, no?

Thanks for the tip about the Selection. You can see I am not a VBA expert: I usually just record a macro via Excel, then do some tweaking via VBA.

[romperstomper]

It's not something I've ever heard of or come across myself. If you can narrow down the steps required to cause it, I will file it as a bug with Microsoft.