How to fix recorded macro that does more when run than while recording?

Question

How to fix recorded macro that does more when run than while recording?

Paul Dalton 125

The Word macro copied below is a "Recorded Macro" that I wanted to work just on a block of text that I would select before invoking the macro. So, before I started to record the macro, I selected a block of text, then I used the Macro Recorder. All I wanted it to do was -- only within that preselected block of text -- first delete all spaces (replace with nothing) and then replace all paragraph marks (^p) with two spaces.

While recording the macro, it did exactly what I wanted and I finished by clicking on Close (to close the Find/Replace), then I clicked on Stop.

Next, I selected a different block of text and ran that newly recorded macro. It quickly popped up a box saying "We made 328 replacements. Do you want to continue searching from the beginning?" I clicked on "No". Then it popped up a similar box saying "We made 61 replacements. Do you want to continue searching from the beginning?" Again, I clicked on "No". Next, I saw that the macro had performed both actions not only on the selected block of text, but also on all text following the selected text block until the end of the document.

How do I edit/revise this recorded macro to cause it to do what I want ONLY on the preselected block of text and not also on the rest of the document?

FWIW, the following 4 lines is an example of a block of text I want to select, then run the macro:

♠ K 8

♥ 5 4

♦ K Q 10 9 5 3

♣ K J 10

And the following is how I want it to appear on just one line after I run the macro:

♠K8 ♥54 ♦KQ10953 ♣KJ10

And the following is the recorded macro that I've copied from the VBA Editor:

Sub HandSpcFix()

'

' HandSpcFix Macro

'

Selection.Find.ClearFormatting

Selection.Find.Replacement.ClearFormatting

With Selection.Find

    .Text = " "

    .Replacement.Text = ""

    .Forward = True

    .Wrap = wdFindAsk

    .Format = False

    .MatchCase = False

    .MatchWholeWord = False

    .MatchWildcards = False

    .MatchSoundsLike = False

    .MatchAllWordForms = False

End With

Selection.Find.Execute Replace:=wdReplaceAll

With Selection.Find

    .Text = "^p"

    .Replacement.Text = "  "

    .Forward = True

    .Wrap = wdFindAsk

    .Format = False

    .MatchCase = False

    .MatchWholeWord = False

    .MatchWildcards = False

    .MatchSoundsLike = False

    .MatchAllWordForms = False

End With

Selection.Find.Execute Replace:=wdReplaceAll

End Sub

0 comments

Answer accepted by question author

AI answer

Replace the recorded Selection.Find operations with searches on a duplicated Range and set Wrap:=wdFindStop, which confines both replacements to the originally selected text.

Sub HandSpcFix()
    Dim scope As Range

    If Selection.Type = wdSelectionIP Then
        MsgBox "Select the text to process first."
        Exit Sub
    End If

    Set scope = Selection.Range
    ReplaceInRange scope.Duplicate, " ", ""
    ReplaceInRange scope.Duplicate, "^p", "  "
End Sub

Private Sub ReplaceInRange(ByVal target As Range, _
                           ByVal findText As String, _
                           ByVal replaceText As String)
    With target.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Execute FindText:=findText, ReplaceWith:=replaceText, _
                 Forward:=True, Wrap:=wdFindStop, _
                 Format:=False, Replace:=wdReplaceAll
    End With
End Sub

A Word Range represents a specific contiguous part of the document and can be manipulated independently of the visible selection.^1^ Range.Duplicate creates another range with the same boundaries, allowing each Find/Replace pass to operate on that scope without moving the user's selection.^2^

The significant setting in the recorded macro is:

.Wrap = wdFindAsk

wdFindAsk tells Word to display a prompt after searching the selection or range and offer to search the rest of the document. wdFindStop ends the search at the boundary instead.^3^ Using Range.Find also avoids the way Selection.Find changes the active selection when it finds text.^4^

Select only the four lines to transform, then run HandSpcFix. The result should be:

♠K8  ♥54  ♦KQ10953  ♣KJ10

If the selection includes the final paragraph mark after ♣ K J 10, that mark will also be replaced and may join the result to the following paragraph. Exclude that final paragraph mark when selecting unless that join is intended.

References

AI-generated content may be incorrect. Read our transparency notes for more information.

1 additional answer

Your answer

Answer 1

Paul Dalton 125

The AI suggested version works as desired. Thanks.

0 comments