Macro to run on all columns except column H

[Lorr81]

Hi I have a macro below which find and replaces countries with reference numbers which is working ok however I need to exclude column H, I have tried adding the range line below but it is not working and I keep getting error message, I would be grateful if any advice.

Sub Multi_FindReplace()
sheetlist = Array("SF Export")
Range ("a1,b1,c1,d1,e,f1,g1,i1,j1,k1,l1,m1,n1,o1,p1,q1,r1")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate

Original Macro:
Sub Multi_FindReplace()
sheetlist = Array("SF Export")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate


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")


Next
End Sub



Thank You,

 

[frank_AL]

Without doing any testing I do see an anomaly in your Range line. you have the first cell in each column ("a1,b1,etc) except for column "e"
Range ("a1,b1,c1,d1,e,f1,g1,i1,j1,k1,l1,m1,n1,o1,p1,q1,r1")

 

[Lorr81]

Thank you for quick response, I have just changed that now but I am receiving the error message saying Invalid use of property. sorry i am really not great with macros.

 

[frank_AL]

Okay, so not any representative data I have modified the code to skip Column H. I've done this by skipping the number 8 in the Select Case statement.

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("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")
            End With
    End Select
    Next
Next
End Sub

 

[Lorr81]

Thank you so much, you have saved my sanity.