Macro not working with button and returning error message.

Lorr81

New Member
Joined
Mar 21, 2016
Messages
38
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
Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It sounds as though you have two routines with the same name.
 

Lorr81

New Member
Joined
Mar 21, 2016
Messages
38
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That means that your worksheet is not called "SF Export" - perhaps it has a leading or trailing space in its name.
 

Lorr81

New Member
Joined
Mar 21, 2016
Messages
38
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.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 21, 2016
Messages
38
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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):

Rich (BB code):
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:

Rich (BB code):
                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.
 

Forum statistics

Threads
1,181,638
Messages
5,931,154
Members
436,778
Latest member
TCarr1958

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top