Need help building macro to find and replace multiple values in selected cells.

Hello everyone! I have been trying to build some macro to find and replace multiple values in selected cells as below, Sub FR() Dim rngCell As Range Dim fndList As Variant Dim rplcList As Varia...

excel

Macros and VBA

Riny_van_Eekelen

Nov 02, 2020

Youngmrr Try it this way:

Sub FR()

Dim fndList As Variant
Dim rplcList As Variant
Dim F As Long
fndList = Array("United Kingdom", "United States", "Australia")
rplcList = Array("UK", "US", "AUS")

For F = 0 To UBound(fndList)
      Selection.Replace What:=fndList(F), Replacement:=rplcList(F), _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:=False, ReplaceFormat:=False       
Next F
End Sub

Riny_van_Eekelen

Platinum Contributor

Nov 02, 2020

Youngmrr Try it this way:

Sub FR()

Dim fndList As Variant
Dim rplcList As Variant
Dim F As Long
fndList = Array("United Kingdom", "United States", "Australia")
rplcList = Array("UK", "US", "AUS")

For F = 0 To UBound(fndList)
      Selection.Replace What:=fndList(F), Replacement:=rplcList(F), _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:=False, ReplaceFormat:=False       
Next F
End Sub

Youngmrr

Copper Contributor

Nov 05, 2020

Riny_van_Eekelen It works!!! Thank you so much for your help! 🙂

Forum Discussion

Need help building macro to find and replace multiple values in selected cells.

Resources