Hi helpers,

Just want to switch the following macro which is based on the selection to doing that on a fixed column (K).

And I want to loop the find and replace to all my active workbooks in the same column (K).


Thank for your help,


Sub find_and_replace()


Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant

fndList = Array("Canada", "United States", "us", "ca", "canada", "united states", "U.S.A", "U.S", "u.s", "u.s.a", "Usa", "USA")
rplcList = Array("CA", "US", "US", "CA", "CA", "US", "US", "US", "US", "US", "US", "US")


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

End Sub