FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,368
- Office Version
- 365
- 2016
- Platform
- Windows
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]
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]
Code:
[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]