Forum Discussion

Youngmrr's avatar
Youngmrr
Copper Contributor
Nov 02, 2020
Solved

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 Variant
Dim F As Long

fndList = Array("United Kingdoms", "United States", "Australia")
rplcList = Array("UK", "US", "AUS")


For F = LBound(fndList) To UBound(fndList)

For Each rngCell In Selection
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Next Rng

Next F

End Sub

 But I am having errors. Can you guys help this?

Many thanks,

Young

excel
Macros and VBA
  • Riny_van_Eekelen's avatar
    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

2 Replies

  • Riny_van_Eekelen's avatar
    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

Resources