Link to home
Start Free TrialLog in
Avatar of FaheemAhmadGul
FaheemAhmadGulFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Word Macro that will search and replace all instances of the word "apple" with "mango" after displaying a message box

I need help with a word Macro that would search for the word "apple" in the active document and replace all the instances of that word with the word "mango". However, before replacing each instance of "apple" it would display a message box with two options ("OK" and "No" on it) and will replace "apple" with "mango" only, if I chose OK option. If I chose "No" the macro should look for the next instanace of "apple" in my document and again display a message box (or a small user form with two buttons on it) and check from me whether I want to replace this second instance. This will happen for each instance of the "apple" in my document. Hope my question is clear.
Thank you for your help.
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

You may try something like this...

Sub ReplaceString()
Dim Ans As String
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
    .Text = "apple"
    .Replacement.Text = "mango"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchKashida = False
    .MatchDiacritics = False
    .MatchAlefHamza = False
    .MatchControl = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With
Ans = MsgBox("Do you replace all the instances of apple to mango?", vbQuestion + vbYesNo, "Confirm please!")
If Ans = vbYes Then
    Selection.Find.Execute Replace:=wdReplaceAll
End If
End Sub

Open in new window

Avatar of FaheemAhmadGul

ASKER

Thank you for response to my request for help. This macro works, but it replaces all instances of "apple" with "mango" when I chose Yes in one go. What I need is that if should replace only one instance of "apple" with "mango" and before replacing the second instance it finds of "apple" in the document, it should display a message box to me again to ask me if I want to replace this second instance with mango also.
Perhaps we need some kind of loop here which should keep running to search for the word "apple" until it cannot find it any more "apple" in the document and each time it does find "apple" it should display a message box to me in the way your code does and take appropriate action based on my choice.
Okay try this...

Sub ReplaceString()
Dim Ans As String
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Ans = MsgBox("Do you want to replace apple with mango?", vbQuestion + vbYesNo, "Confirm please!")
Do While Ans = vbYes
    With Selection.Find
        .Text = "apple"
        .Replacement.Text = "mango"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchKashida = False
        .MatchDiacritics = False
        .MatchAlefHamza = False
        .MatchControl = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceOne
    Ans = MsgBox("Do you want to replace another instance of apple with mango?", vbQuestion + vbYesNo, "Confirm please!")
Loop
End Sub

Open in new window

This is better. However, we need to improve it a little. The code we currently have kind of expects the user to know how many instances of the word "apple" are there in my document. I think a better solution would be for the code to first count how many instances of the word "apple" are there in the document. It should then store that number in an integar variable such appleNumbers. And the Do While loop should then run until the value in appleNumbers is more than zero. At the end each run of the code the code should set appleNumbers equal to previous appleNumbers - 1. In this way the loop will stop running automatically after all "apples" have been dealt with.
Hope this makes sense.
Okay. Give this a try.
Sub ReplaceString()
Dim Ans As String
Dim Rng As Range
Dim i As Long, j As Long

Set Rng = ActiveDocument.Range
With Rng.Find
    .Text = "apple"
    Do While .Execute(Forward:=True)
        i = i + 1
    Loop
End With
j = i
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting

Ans = MsgBox("There are " & i & " occurrences of apple in the document." & vbNewLine & "Do you want to replace apple with mango?", vbQuestion + vbYesNo, "Confirm please!")
Do While Ans = vbYes
    With Selection.Find
        .Text = "apple"
        .Replacement.Text = "mango"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchKashida = False
        .MatchDiacritics = False
        .MatchAlefHamza = False
        .MatchControl = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceOne
    i = i - 1
    If i > 0 Then
        Ans = MsgBox("Occurrences of apple left: " & i & " of " & j & vbNewLine & "Do you want to replace another instance of apple with mango?", vbQuestion + vbYesNo, "Confirm please!")
    Else
        MsgBox "There is no occurrence of apple left in the document."
        Exit Sub
    End If
Loop
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Brilliant!!
This worked great. Many thanks. Greatly appreciated.
You're welcome Faheem! Glad it worked as per your requirement.
Thanks for the feedback.