SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1843397%22%20slang%3D%22en-US%22%3ENeed%20help%20building%20macro%20to%20find%20and%20replace%20multiple%20values%20in%20selected%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843397%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone!%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20to%20build%20some%20macro%20to%20find%20and%20replace%20multiple%20values%20in%20selected%20cells%20as%20below%2C%3C%2FP%3E%3CP%3E%26nbsp%3BSub%20FR()%3C%2FP%3E%3CP%3EDim%20rngCell%20As%20Range%3CBR%20%2F%3EDim%20fndList%20As%20Variant%3CBR%20%2F%3EDim%20rplcList%20As%20Variant%3CBR%20%2F%3EDim%20F%20As%20Long%3C%2FP%3E%3CP%3EfndList%20%3D%20Array(%22United%20Kingdoms%22%2C%20%22United%20States%22%2C%20%22Australia%22)%3CBR%20%2F%3ErplcList%20%3D%20Array(%22UK%22%2C%20%22US%22%2C%20%22AUS%22)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFor%20F%20%3D%20LBound(fndList)%20To%20UBound(fndList)%3C%2FP%3E%3CP%3EFor%20Each%20rngCell%20In%20Selection%3CBR%20%2F%3Esht.Cells.Replace%20What%3A%3DfndList(x)%2C%20Replacement%3A%3DrplcList(x)%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20_%3CBR%20%2F%3ESearchFormat%3A%3DFalse%2C%20ReplaceFormat%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20Rng%3C%2FP%3E%3CP%3ENext%20F%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3BBut%20I%20am%20having%20errors.%20Can%20you%20guys%20help%20this%3F%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3EYoung%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1843397%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1843426%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20building%20macro%20to%20find%20and%20replace%20multiple%20values%20in%20selected%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747635%22%20target%3D%22_blank%22%3E%40Youngmrr%3C%2FA%3E%26nbsp%3BTry%20it%20this%20way%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20FR()%0A%0ADim%20fndList%20As%20Variant%0ADim%20rplcList%20As%20Variant%0ADim%20F%20As%20Long%0AfndList%20%3D%20Array(%22United%20Kingdom%22%2C%20%22United%20States%22%2C%20%22Australia%22)%0ArplcList%20%3D%20Array(%22UK%22%2C%20%22US%22%2C%20%22AUS%22)%0A%0AFor%20F%20%3D%200%20To%20UBound(fndList)%0A%20%20%20%20%20%20Selection.Replace%20What%3A%3DfndList(F)%2C%20Replacement%3A%3DrplcList(F)%2C%20_%0A%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20_%0A%20%20%20%20%20%20SearchFormat%3A%3DFalse%2C%20ReplaceFormat%3A%3DFalse%20%20%20%20%20%20%20%0ANext%20F%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1855385%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20building%20macro%20to%20find%20and%20replace%20multiple%20values%20in%20selected%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1855385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BIt%20works!!!%20Thank%20you%20so%20much%20for%20your%20help!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

2 Replies
Highlighted
Best Response confirmed by cuong (Microsoft)
Solution

@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
Highlighted

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