Edit macro to search specific column in sheet not all active sheets.

[Lorr81]

I have the following macro which I have edited and would be grateful if some could please help me edit it so it only looks sheet1, also have I done this correctly so that it will find and replace?

Sub Multi_FindReplace()




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long


fndList = Array("Libya", "New Caledonia", "St Vincent and the Grenadines", "Tanzania Untd Republic of", "Liechtenstein", "New Zealand", "Saint Pierre and Miquelon", "Thailand", "Lithuania")
rplcList = Array("434", "540", "670", "834", "438", "554", "666", "764", "440")
fndList = Array("Nicaragua", "Samoa", "Timor -Leste", "Luxembourg", "Niger", "San Marino", "Togo", "Macao", "Nigeria", "Sao Tome And Principe", "Tokelau")
rplcList = Array("558", "882", "626", "442", "562", "674", "768", "446", "566", "678", "772")
fndList = Array("Macedonia the former Yugoslav Republic of", "Niue", "Saudi Arabia", "Tonga", "Madagascar", "Norfolk Island", "Senegal", "Trinidad and Tobago", "Malawi", "Northern Mariana Islands", "Serbia", "Tunisia")
rplcList = Array("807", "570", "682", "776", "450", "574", "686", "780", "454", "580", "688", "788")
fndList = Array("Malaysia", "Norway", "Seychelles", "Turkey", "Maldives", "Oman", "Sierra Leone", "Turkmenistan", "Mali", "Pakistan", "Singapore", "Turks and Caicos Islands", "Malta", "Palau", "Sint Martin (Dutch part)", "Tuvalu", "Marshall Islands", "Palestine, State of")
rplcList = Array("458", "578", "690", "792", "462", "512", "694", "795", "466", "586", "702", "796", "470", "585", "534", "798", "584", "275")
fndList = Array("Slovakia", "Uganda", "Martinique", "Panama", "Slovenia", "Ukraine", "Mauritania", "Papua New Guinea", "Solomon Islands", "United Arab Emirates")
rplcList = Array("703", "800", "474", "591", "705", "804", "478", "598", "90", "784")
fndList = Array("Mauritius", "Paraguay", "Somalia", "United Kingdom", "Mayotte", "Peru", "South Africa", "United States", "Mexico", "Philippines", "South Georgia and the South Sandwich Islands")
rplcList = Array("480", "600", "706", "826", "175", "604", "710", "840", "484", "608", "239")
fndList = Array("US Minor Outlying Islands", "Micronesia Federated States of", "Pitcairn", "South Sudan", "Unknown", "Moldova", "Poland", "Spain", "Uruguay", "Monaco", "Portugal", "Sri Lanka", "Uzbekistan")
rplcList = Array("581", "583", "612", "728", "999", "498", "616", "724", "858", "492", "620", "144", "860")
fndList = Array("Mongolia", "Puerto Rico", "St Helena Ascension & Tristan da Cunha", "Vanuatu", "Montenegro", "Qatar", "Sudan", "Venezuela", "Montserrat", "Reunion", "Suriname", "Viet Nam")
rplcList = Array("496", "630", "654", "548", "499", "634", "736", "862", "500", "638", "740", "704")
fndList = Array("Morocco", "Romania", "Svalbard and Jan Mayen", "Virgin Islands British", "Myanmar", "Russian Federation", "Swaziland", "Virgin Islands U.S.", "Mozambique", "Rwanda", "Sweden", "Wallis and Futuna", "Namibia", "Saint Barthelemy")
rplcList = Array("504", "642", "744", "92", "104", "643", "748", "850", "508", "646", "752", "876", "516", "652")
fndList = Array("Switzerland", "Western Sahara", "Nauru", "Saint Kitts and Nevis", "Syrian Arab Republic", "Yemen", "Nepal", "Saint Lucia", "Taiwan Province of China", "Zambia", "Netherlands", "Saint Martin (French part)", "Tajikistan", "Zimbabwe")
rplcList = Array("756", "732", "520", "659", "760", "887", "524", "662", "158", "894", "528", "663", "762", "716")


'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x





End Sub

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel...f-x-then-return-y-next-sheet.html#post4970285

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.