Macro not working with button and returning error message.

[Lorr81]

Hi I have the following macro which was working but when I add it to a button I get an error message saying Ambiguous Name: Multi_FindReplace when I click the button to run the macro.

when I debug the row with orange text below is highlighted in yellow.

I would be grateful if you could please help.

Lorr

Option Explicit




Sub Multi_FindReplace()
Dim sheetlist As Variant
Dim i As Long
Dim x As Integer
Dim lastrow As Long
Dim findrng As Range
Dim fndList As Variant




Dim rplcList As Variant




sheetlist = Array("SF Export")
For i = LBound(sheetlist) To UBound(sheetlist)
    Worksheets(sheetlist(i)).Activate
    
    For x = 1 To 18
    Select Case x
        Case 1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18
            lastrow = Cells(Rows.Count, x).End(xlUp).Row
            Set findrng = Range(Cells(1, x), Cells(lastrow, x))
            With findrng
                fndList = Array("Afghanistan", "Aland Islands", "Albania", "Algeria", "American Samoa", "Andorra", "Angola", "Anguilla", "Antarctica", "Antigua and Barbuda")
                rplcList = Array("4", "248", "8", "12", "16", "20", "24", "660", "10", "28")
                fndList = Array("Argentina", "Armenia", "Aruba", "Australia", "Austria", "Azerbaijan", "Bahamas", "Bahrain", "Bangladesh", "Barbados")
                rplcList = Array("32", "51", "533", "36", "40", "31", "44", "48", "50", "52")
                fndList = Array("Belarus", "Belgium", "Belize", "Benin", "Bermuda", "Bhutan", "Bolivia", "Bonaire, Saint Eustatius and Saba", "Bosnia and Herzegovina", "Botswana")
                rplcList = Array("112", "56", "84", "204", "60", "64", "68", "535", "70", "72")
                fndList = Array("Bouvet Island", "Brazil", "British Indian Ocean Territory", "Brunei Darussalam", "Bulgaria", "Burkina Faso", "Burundi", "Cambodia", "Cameroon", "Canada")
                rplcList = Array("74", "76", "86", "96", "100", "854", "108", "116", "120", "124")
                fndList = Array("Cape Verde", "Cayman Islands", "Central African Republic", "Chad", "Chile", "China", "Christmas Island", "Cocos (Keeling) Islands", "Colombia", "Comoros")
                rplcList = Array("132", "136", "140", "148", "152", "156", "162", "166", "170", "174")
                fndList = Array("Congo", "Congo Democratic Republic of the", "Cook Islands", "Costa Rica", "Cote d'Ivoire", "Croatia", "Cuba", "Curacao", "Cyprus", "Czech Republic")
                rplcList = Array("178", "180", "184", "188", "384", "191", "192", "531", "196", "203")
                fndList = Array("Denmark", "Djibouti", "Dominica", "Dominican Republic", "Ecuador", "Egypt", "El Salvador", "Equatorial Guinea", "Eritrea", "Estonia")
                rplcList = Array("208", "262", "212", "214", "218", "818", "222", "226", "232", "233")
                fndList = Array("Ethiopia", "Falkland Islands (Malvinas)", "Faroe Islands", "Fiji", "Finland", "France", "French Guiana", "French Polynesia", "French Southern Territories", "Gabon")
                rplcList = Array("231", "238", "234", "242", "246", "250", "254", "258", "260", "266")
                fndList = Array("Gambia", "Georgia", "Germany", "Ghana", "Gibraltar", "Greece", "Greenland", "Grenada", "Guadeloupe", "Guam")
                rplcList = Array("270", "268", "276", "288", "292", "300", "304", "308", "312", "316")
                fndList = Array("Guatemala", "Guernsey", "Guinea", "Guinea-Bissau", "Guyana", "Haiti", "Heard Island and McDonald Islands", "Holy See (Vatican City State)", "Honduras", "Hong Kong")
                rplcList = Array("320", "831", "324", "624", "328", "332", "334", "336", "340", "344")
                fndList = Array("Hungary", "Iceland", "India", "Indonesia", "Iran Islamic Republic of", "Iraq", "Ireland", "Israel", "Italy", "Jamaica")
                rplcList = Array("348", "352", "356", "360", "364", "368", "372", "376", "380", "388")
                fndList = Array("Japan", "Jersey", "Jordan", "Kenya", "Kiribati", "Korea Democratic People's Republic of", "Korea Republic of", "Kosovo", "Kuwait", "Kyrgyzstan")
                rplcList = Array("392", "832", "400", "404", "296", "408", "410", "995", "414", "417")
                fndList = Array("Latvia", "Lebanon", "Lesotho", "Liberia", "Libya", "Liechtenstein", "Lithuania", "Luxembourg", "Macao", "Macedonia the former Yugoslav Republic of")
                rplcList = Array("428", "422", "426", "430", "434", "438", "440", "442", "446", "807")
                fndList = Array("Madagascar", "Malawi", "Malaysia", "Maldives", "Mali", "Malta", "Marshall Islands", "Martinique", "Mauritania", "Mauritius")
                rplcList = Array("450", "454", "458", "462", "466", "470", "584", "474", "478", "480")
                fndList = Array("Mayotte", "Mexico", "Micronesia Federated States of", "Moldova", "Monaco", "Mongolia", "Montenegro", "Montserrat", "Morocco", "Mozambique")
                rplcList = Array("175", "484", "583", "498", "492", "496", "499", "500", "504", "508")
                fndList = Array("Myanmar", "Namibia", "Nauru", "Nepal", "Netherlands", "New Caledonia", "New Zealand", "Nicaragua", "Niger", "Nigeria")
                rplcList = Array("104", "516", "520", "524", "528", "540", "554", "558", "562", "566")
                fndList = Array("Niue", "Norfolk Island", "Northern Mariana Islands", "Norway", "Oman", "Pakistan", "Palau", "Palestine, State of", "Panama", "Papua New Guinea")
                rplcList = Array("570", "574", "580", "578", "512", "586", "585", "275", "591", "598")
                fndList = Array("Paraguay", "Peru", "Philippines", "Pitcairn", "Poland", "Portugal", "Puerto Rico", "Qatar", "Reunion", "Romania")
                rplcList = Array("600", "604", "608", "612", "616", "620", "630", "634", "638", "642")
                fndList = Array("Russian Federation", "Rwanda", "Saint Barthelemy", "Saint Kitts and Nevis", "Saint Lucia", "Saint Martin (French part)", "Saint Pierre and Miquelon", "Samoa", "San Marino", "Sao Tome and Principe")
                rplcList = Array("643", "646", "652", "659", "662", "663", "666", "882", "674", "678")
                fndList = Array("Saudi Arabia", "Senegal", "Serbia", "Seychelles", "Sierra Leone", "Singapore", "Sint Martin (Dutch part)", "Slovakia", "Slovenia", "Solomon Islands")
                rplcList = Array("682", "686", "688", "690", "694", "702", "534", "703", "705", "90")
                fndList = Array("Somalia", "South Africa", "South Georgia and the South Sandwich Islands", "South Sudan", "Spain", "Sri Lanka", "St Helena Ascension & Tristan da Cunha", "St Vincent and the Grenadines", "Sudan", "Suriname")
                rplcList = Array("706", "710", "239", "728", "724", "144", "654", "670", "736", "740")
                fndList = Array("Svalbard and Jan Mayen", "Swaziland", "Sweden", "Switzerland", "Syrian Arab Republic", "Taiwan Province of China", "Tajikistan", "Tanzania Untd Republic of", "Thailand", "Timor-Leste")
                rplcList = Array("744", "748", "752", "756", "760", "158", "762", "834", "764", "626")
                fndList = Array("Togo", "Tokelau", "Tonga", "Trinidad and Tobago", "Tunisia", "Turkey", "Turkmenistan", "Turks and Caicos Islands", "Tuvalu", "Uganda")
                rplcList = Array("768", "772", "776", "780", "788", "792", "795", "796", "798", "800")
                fndList = Array("Ukraine", "United Arab Emirates", "United Kingdom", "United States", "Unknown", "Uruguay", "US Minor Outlying Islands", "Uzbekistan", "Vanuatu", "Venezuela")
                rplcList = Array("804", "784", "826", "840", "999", "858", "581", "860", "548", "862")
                fndList = Array("Viet Nam", "Virgin Islands British", "Virgin Islands U.S.", "Wallis and Futuna", "Western Sahara", "Yemen", "Zambia", "Zimbabwe")
                rplcList = Array("704", "92", "850", "876", "732", "887", "894", "716")
                fndList = Array("Male", "Female", "Unknown")
                rplcList = Array("1", "2", "3")
                fndList = Array("Adult Care Sector ? Local Authority", "Adult Care Sector ? Private or voluntary sector", "Agency")
                rplcList = Array("1", "4", "12")
                fndList = Array("Children?s sector ? Local Authority", "Children?s sector ? Private or voluntary sector", "From abroad")
                rplcList = Array("3", "4", "9")
                fndList = Array("Health sector", "Not known", "Not previously employed")
                rplcList = Array("5", "16", "10")
                fndList = Array("Other sector", "Other sources", "Retail sector")
                rplcList = Array("7", "15", "6")
                fndList = Array("Returner", "Student work experience or placement", "Volunteering or voluntary work")
                rplcList = Array("11", "13", "14")
                
                
                End With
    End Select
    Next
Next
End Sub

 

[RoryA]

It sounds as though you have two routines with the same name.

 

[Lorr81]

Thank you for quick you were correct I had a duplicate module, I have deleted that and now receive an error message saying out of range with the Worksheets(sheetlist(i)).Activate highlighted in yellow, do I need to rename this?

 

[RoryA]

That means that your worksheet is not called "SF Export" - perhaps it has a leading or trailing space in its name.

 

[Lorr81]

Thank you Rory however I have just checked and they are the same, I am now getting no error message but no results are being returned.

 

[RoryA]

Having now looked through the rest of your code, it doesn't actually do anything other than set some variables repeatedly, nor is it clear to me what it is supposed to do, I'm afraid.

 

[Lorr81]

Ah I see! I have a sheet of data then I need to convert from text to numeric as follows
if the the country of birth was Afghanistan then change this to 4 or if the country of birth was the Uk then change this to 826 also if Female is in the gender field his should be changed to the number 2.I then have a secondary macro which pulls this data to the desired column in a second sheet.
I is really a find and replace but I have obviously done something wrong.

 

[RoryA]

I is really a find and replace but I have obviously done something wrong.

Yup - there is no code there to actually do any replacing!

 

[Lorr81]

So is the rplcList = Array not the replace option?

 

[RoryA]

No, it's just assigning an array to a variable, not actually doing anything with it.

I'd suggest you add a new routine like this (untested):

Sub ReplaceItems(rg as Range, originalData, newData)
dim n as long
for n = lbound(originalData) to ubound(originalData)
rg.Replace originalData(n), newData(n), xlWhole
next n
end sub

and then amend your code to add a call to that between each assignment pair like this:

                fndList = Array("Afghanistan", "Aland Islands", "Albania", "Algeria", "American Samoa", "Andorra", "Angola", "Anguilla", "Antarctica", "Antigua and Barbuda")
                rplcList = Array("4", "248", "8", "12", "16", "20", "24", "660", "10", "28")
Call ReplaceItems(findrng, fndList, rplcList)

and repeat that blue line after each change in the lists.