VBA: Find and replace words in column

[FryGirl]

I've tried to craft some VBA to find and replace an array of words but this macro replaces words not in order.

I started with code found here. [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all

Basically I'm adding an "S" to the end of certain works to make them plural. I do have at least one word which will need an "ES" to makes it plural.[/FONT]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub MultiFindReplace()
    Dim rplcList    As Variant
    Dim fndList     As Variant
    Dim MyCell      As Range
    Dim x           As Long
    
    fndList = Array("MANAGE", "REVIEW", "COLLECT", "DEFINE", "DETERMINE", "PROCESS")
    rplcList = Array("MANAGES", "REVIEWS", "COLLECTS", "DEFINES", "DETERMINES", "PROCESSES")
    
    For Each MyCell In Range("C2", Range("C" & Rows.Count).End(xlUp))
        For x = LBound(fndList) To UBound(fndList)
            MyCell.Replace What:=fndList(x), Replacement:=rplcList, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Next x
    Next MyCell
    
End Sub
[/FONT]

 

[Akuini]

Maybe this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] MultiFindReplace1()
    [COLOR=Royalblue]Dim[/COLOR] rplcList    [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] fndList     [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] MyCell      [COLOR=Royalblue]As[/COLOR] Range
    [COLOR=Royalblue]Dim[/COLOR] x           [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    
    fndList = Array([COLOR=brown]"MANAGE"[/COLOR], [COLOR=brown]"REVIEW"[/COLOR], [COLOR=brown]"COLLECT"[/COLOR], [COLOR=brown]"DEFINE"[/COLOR], [COLOR=brown]"DETERMINE"[/COLOR], [COLOR=brown]"PROCESS"[/COLOR])
    rplcList = Array([COLOR=brown]"MANAGES"[/COLOR], [COLOR=brown]"REVIEWS"[/COLOR], [COLOR=brown]"COLLECTS"[/COLOR], [COLOR=brown]"DEFINES"[/COLOR], [COLOR=brown]"DETERMINES"[/COLOR], [COLOR=brown]"PROCESSES"[/COLOR])
        
        [COLOR=Royalblue]For[/COLOR] x = LBound(fndList) [COLOR=Royalblue]To[/COLOR] UBound(fndList)
            Range([COLOR=brown]"C2"[/COLOR], Range([COLOR=brown]"C"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp)).Replace What:=fndList(x), Replacement:=rplcList(x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR], ReplaceFormat:=[COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]Next[/COLOR] x
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[FryGirl]

Hi Akuini,

This is perfect. Thank you.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[billandrew]

I've searched a bit, Is there a way to reference the FindList & Replace List to a range.

ex. fndList = Range("I2:I7")

Thank You

 

[Akuini]

I've searched a bit, Is there a way to reference the FindList & Replace List to a range.

ex. fndList = Range("I2:I7")

Maybe this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] MultiFindReplace2()
    [COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
        [I][COLOR=seagreen]'find list in col I & replace list in col J, in Range("I2:J7")[/COLOR][/I]
        [COLOR=Royalblue]Set[/COLOR] c = Range([COLOR=brown]"I2:J7"[/COLOR])
        [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] c.Rows.count
            Range([COLOR=brown]"C2"[/COLOR], Range([COLOR=brown]"C"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp)).Replace What:=c.Cells(i, [COLOR=crimson]1[/COLOR]), Replacement:=c.Cells(i, [COLOR=crimson]2[/COLOR]), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR], ReplaceFormat:=[COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]Next[/COLOR] i
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[billandrew]

Works very well!

Thank You.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.