Second occurrence.

Question

Hello from Steve

At this time it is updating both names.

Objective please is to update the second occurrence "(ReplArray)"

Sub Rename_Trainers_Names()    
    FindArray = Array("K PARSONS", "SHARROCK NEW", "L STEWART", "H BROSNAN", "T CARTER", _   
    "M PITMAN", "MAHONEY BYERLEY", "PATTERSON NEW", "E CLOTWORTHY", "J BROSNAN", "DE LAUTOUR", _    
    "T DIDHAM", "COOKSLEY BYERLEY", "HOSKIN BYERLEY", "K ALEXANDER", "GRAY PALMERSTON", "BREEZE O"    
    ReplArray = Array("J & K PARSONS", "A SHARROCK", "L & L STEWART", "M BROSNAN", "M & T CARTER", _    
    "M & M PITMAN", "J MAHONEY", "R PATTERSON", "S & E CLOTWORTHY", "P & J BROSNAN", "L DE LAUTOUR", _  
    "P & T DIDHAM", "WALLACE COOKSLEY", "K HOSKIN", "S @ K ALEXANDER", "K & S GRAY", "OSULLIVAN SCOTT")    
    For i = 0 To UBound(FindArray)      
        Options.DefaultHighlightColorIndex = wdNoHighlight     
        With Selection.Find            
            .Font.Size = 14.5        
            .Text = FindArray(i)        
            .Replacement.Text = ReplArray(i)           
            .Forward = True        
            .Wrap = wdFindContinue         
            .Format = False            
            .MatchCase = True           
            .MatchWholeWord = True            
            .MatchWildcards = False            
            .MatchSoundsLike = False            
            .MatchAllWordForms = False          
        End With     
        Selection.Find.Execute Replace
         = wdReplaceAll     
    Next i

Answer 1

Hello Steve Dee, thank you for reaching out to Microsoft Q&A forum.

The reason your current code updates both occurrences is because of the last line:

Selection.Find.Execute Replace = wdReplaceAll

That command tells Word to replace all matches of the search term in the document. If you only want to change the second occurrence, you need to search for the term repeatedly, keep a counter, and replace only when the counter equals 2.

Here’s an example of how you can do that:

Sub Rename_Trainers_Names()
    Dim FindArray As Variant, ReplArray As Variant
    Dim i As Long, count As Long
    
    FindArray = Array("K PARSONS", "SHARROCK NEW", "L STEWART", "H BROSNAN", "T CARTER", _
                      "M PITMAN", "MAHONEY BYERLEY", "PATTERSON NEW", "E CLOTWORTHY", "J BROSNAN", _
                      "DE LAUTOUR", "T DIDHAM", "COOKSLEY BYERLEY", "HOSKIN BYERLEY", "K ALEXANDER", _
                      "GRAY PALMERSTON", "BREEZE O")
    
    ReplArray = Array("J & K PARSONS", "A SHARROCK", "L & L STEWART", "M BROSNAN", "M & T CARTER", _
                      "M & M PITMAN", "J MAHONEY", "R PATTERSON", "S & E CLOTWORTHY", "P & J BROSNAN", _
                      "L DE LAUTOUR", "P & T DIDHAM", "WALLACE COOKSLEY", "K HOSKIN", "S & K ALEXANDER", _
                      "K & S GRAY", "OSULLIVAN SCOTT")
    
    For i = LBound(FindArray) To UBound(FindArray)
        count = 0
        Selection.HomeKey wdStory 'Start from beginning of document
        
        With Selection.Find
            .ClearFormatting
            .Text = FindArray(i)
            .Forward = True
            .Wrap = wdFindStop
            .MatchCase = True
            .MatchWholeWord = True
            .MatchWildcards = False
        End With
        
        'Loop through matches and replace only the second occurrence
        Do While Selection.Find.Execute
            count = count + 1
            If count = 2 Then
                Selection.Text = ReplArray(i)
                Exit Do
            End If
        Loop
    Next i
End Sub

Why this works:

• It removes Replace:=wdReplaceAll so you don’t replace everything.
• It loops through matches and uses a counter.
• It stops after replacing the second occurrence for each name.

If the second occurrence doesn’t exist, the code just moves on to the next name. You can add error handling or logging if needed. I hope this helps!

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.