Problem with running 2 different macros

pupsia · Jun 1, 2016

Hello,

I have a bit of a problem, I have lots of different macros in one Excel, and use them depending on the situation, sometimes I use only a few, sometime all of them. But there is a constant, the last macro I use is the same all the time, its to reorganize columns.

The problem is, that when I use one of the macros (lets call it Country Codes) before the last one (lets call it Reorganize), the Reorganize doesn't work normally, some of the columns are missing (the columns that are missing after the macro are: A, N, P, V, X, Y, Z, AA, AB, AC, AG, AH, AN)
As I don't use the Country Codes macro that often, it didn't impact the rest of my work, if I had to use it, after using the Country Codes macro, I would open a new Excel file, copy/past the corrected data, and only then use Reorganize macro. Its fine if I do it only once in a while, but lately I need to do it more and some of my colleagues are starting to use my macro, and its very inconvenient to copy/past all the time.

No matter what I try, I wasn't able to fix this problem, I wasn't even able to find out why this is happening, it took me a while to figure out, what macros do not work together, because this problems doesn't occur all the time.

I`m adding both macros here, sorry if they are long and confusing, I`m not that good with macro yet, so I dont know all the ways to write macros.

By the way, the Country Codes macro consists of 4 macros put together.

Hope I made sense... Please help...

Country Codes

Code:

Sub ChangeCountry()

Sheets("Bulk").Select

ConvertToUpperCase
ChangeCountryBene
ChangeCountryBank
ChangeCountryIBK

End Sub

Code:

Sub ConvertToUpperCase()

Dim Rng As Range

Sheets("Bulk").Select

        For Each Rng In ActiveSheet.UsedRange.Cells
            If Rng.HasFormula = False Then 'Ensures Formulas are bypassed
                Rng.Value = UCase(Rng.Value)
            End If
        Next Rng
        
        For Each Rng In Range("A1:BL1")
            Rng.Value = StrConv(Rng.Value, vbProperCase)
        Next Rng

End Sub

Code:

Sub ChangeCountryBene()

Dim Cell As Object

 For Each Cell In Sheets("Bulk").Range("Q:Q")

Do While InStr(Cell, "AFGHANISTAN") > 0
Cell.Value = Replace(Cell, "AFGHANISTAN", "AF")
Loop
Do While InStr(Cell, "ALBANIA") > 0
Cell.Value = Replace(Cell, "ALBANIA", "AL")
Loop
Do While InStr(Cell, "ALGERIA") > 0
Cell.Value = Replace(Cell, "ALGERIA", "DZ")
Loop
Do While InStr(Cell, "AMERICAN SAMOA") > 0
Cell.Value = Replace(Cell, "AMERICAN SAMOA", "AS")
Loop
Do While InStr(Cell, "ANDORRA") > 0
Cell.Value = Replace(Cell, "ANDORRA", "AD")
Loop
Do While InStr(Cell, "ANGOLA") > 0
Cell.Value = Replace(Cell, "ANGOLA", "AO")
Loop
Do While InStr(Cell, "ANGUILLA") > 0
Cell.Value = Replace(Cell, "ANGUILLA", "AI")
Loop
Do While InStr(Cell, "ANTARCTICA") > 0
Cell.Value = Replace(Cell, "ANTARCTICA", "AQ")
Loop
Do While InStr(Cell, "ANTIGUA AND BARBUDA") > 0
Cell.Value = Replace(Cell, "ANTIGUA AND BARBUDA", "AG")
Loop
Do While InStr(Cell, "ANTIGUA & BARBUDA") > 0
Cell.Value = Replace(Cell, "ANTIGUA & BARBUDA", "AG")
Loop
Do While InStr(Cell, "ARGENTINA") > 0
Cell.Value = Replace(Cell, "ARGENTINA", "AR")
Loop
Do While InStr(Cell, "ARMENIA") > 0
Cell.Value = Replace(Cell, "ARMENIA", "AM")
Loop
Do While InStr(Cell, "ARUBA") > 0
Cell.Value = Replace(Cell, "ARUBA", "AW")
Loop
Do While InStr(Cell, "AUSTRALIA") > 0
Cell.Value = Replace(Cell, "AUSTRALIA", "AU")
Loop
Do While InStr(Cell, "AUSTRIA") > 0
Cell.Value = Replace(Cell, "AUSTRIA", "AT")
Loop
Do While InStr(Cell, "AZERBAIJAN") > 0
Cell.Value = Replace(Cell, "AZERBAIJAN", "AZ")
Loop
Do While InStr(Cell, "BAHAMAS") > 0
Cell.Value = Replace(Cell, "BAHAMAS", "BS")
Loop
Do While InStr(Cell, "BAHRAIN") > 0
Cell.Value = Replace(Cell, "BAHRAIN", "BH")
Loop
Do While InStr(Cell, "BANGLADESH") > 0
Cell.Value = Replace(Cell, "BANGLADESH", "BD")
Loop
Do While InStr(Cell, "BARBADOS") > 0
Cell.Value = Replace(Cell, "BARBADOS", "BB")
Loop
Do While InStr(Cell, "BELARUS") > 0
Cell.Value = Replace(Cell, "BELARUS", "BY")
Loop
Do While InStr(Cell, "BELGIUM") > 0
Cell.Value = Replace(Cell, "BELGIUM", "BE")
Loop
Do While InStr(Cell, "BELIZE") > 0
Cell.Value = Replace(Cell, "BELIZE", "BZ")
Loop
Do While InStr(Cell, "BENIN") > 0
Cell.Value = Replace(Cell, "BENIN", "BJ")
Loop
Do While InStr(Cell, "BERMUDA") > 0
Cell.Value = Replace(Cell, "BERMUDA", "BM")
Loop
Do While InStr(Cell, "BHUTAN") > 0
Cell.Value = Replace(Cell, "BHUTAN", "BT")
Loop
Do While InStr(Cell, "BOLIVIA") > 0
Cell.Value = Replace(Cell, "BOLIVIA", "BO")
Loop
Do While InStr(Cell, "BOSNIA-HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA-HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOSNIA AND HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA AND HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOSNIA & HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA & HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOTSWANA") > 0
Cell.Value = Replace(Cell, "BOTSWANA", "BW")
Loop
Do While InStr(Cell, "BOUVET ISLAND") > 0
Cell.Value = Replace(Cell, "BOUVET ISLAND", "BV")
Loop
Do While InStr(Cell, "BRAZIL") > 0
Cell.Value = Replace(Cell, "BRAZIL", "BR")
Loop
Do While InStr(Cell, "BRITISH INDIAN OCEAN TERRITORY") > 0
Cell.Value = Replace(Cell, "BRITISH INDIAN OCEAN TERRITORY", "IO")
Loop
Do While InStr(Cell, "BRUNEI DARUSSALAM") > 0
Cell.Value = Replace(Cell, "BRUNEI DARUSSALAM", "BN")
Loop
Do While InStr(Cell, "BULGARIA") > 0
Cell.Value = Replace(Cell, "BULGARIA", "BG")
Loop
Do While InStr(Cell, "BURKINA FASO") > 0
Cell.Value = Replace(Cell, "BURKINA FASO", "BF")
Loop
Do While InStr(Cell, "BURUNDI") > 0
Cell.Value = Replace(Cell, "BURUNDI", "BI")
Loop
Do While InStr(Cell, "CAMBODIA") > 0
Cell.Value = Replace(Cell, "CAMBODIA", "KH")
Loop
Do While InStr(Cell, "CAMEROON") > 0
Cell.Value = Replace(Cell, "CAMEROON", "CM")
Loop
Do While InStr(Cell, "CANADA") > 0
Cell.Value = Replace(Cell, "CANADA", "CA")
Loop
Do While InStr(Cell, "CAPE VERDE") > 0
Cell.Value = Replace(Cell, "CAPE VERDE", "CV")
Loop
Do While InStr(Cell, "CAYMAN ISLANDS") > 0
Cell.Value = Replace(Cell, "CAYMAN ISLANDS", "KY")
Loop
Do While InStr(Cell, "CENTRAL AFRICAN REPUBLIC") > 0
Cell.Value = Replace(Cell, "CENTRAL AFRICAN REPUBLIC", "CF")
Loop
Do While InStr(Cell, "CHAD") > 0
Cell.Value = Replace(Cell, "CHAD", "TD")
Loop
Do While InStr(Cell, "CHILE") > 0
Cell.Value = Replace(Cell, "CHILE", "CL")
Loop
Do While InStr(Cell, "CHINA") > 0
Cell.Value = Replace(Cell, "CHINA", "CN")
Loop
Do While InStr(Cell, "CHRISTMAS ISLAND") > 0
Cell.Value = Replace(Cell, "CHRISTMAS ISLAND", "CX")
Loop
Do While InStr(Cell, "COCOS (KEELING) ISLANDS") > 0
Cell.Value = Replace(Cell, "COCOS (KEELING) ISLANDS", "CC")
Loop
Do While InStr(Cell, "COCOS ISLANDS") > 0
Cell.Value = Replace(Cell, "COCOS ISLANDS", "CC")
Loop
Do While InStr(Cell, "COCOS") > 0
Cell.Value = Replace(Cell, "COCOS", "CC")
Loop
Do While InStr(Cell, "KEELING ISLANDS") > 0
Cell.Value = Replace(Cell, "KEELING ISLANDS", "CC")
Loop
Do While InStr(Cell, "COLOMBIA") > 0
Cell.Value = Replace(Cell, "COLOMBIA", "CO")
Loop
Do While InStr(Cell, "COMOROS") > 0
Cell.Value = Replace(Cell, "COMOROS", "KM")
Loop
Do While InStr(Cell, "CONGO") > 0
Cell.Value = Replace(Cell, "CONGO", "CG")
Loop
Do While InStr(Cell, "REPUBLIC OF THE CONGO") > 0
Cell.Value = Replace(Cell, "REPUBLIC OF THE CONGO", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC OF THE") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC OF THE", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC OF", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC", "CG")
Loop
Do While InStr(Cell, "CONGO, DEM. REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, DEM. REPUBLIC", "CD")
Loop
Do While InStr(Cell, "DEMOCRATIC REPUBLIC OF THE CONGO") > 0
Cell.Value = Replace(Cell, "DEMOCRATIC REPUBLIC OF THE CONGO", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC OF THE") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC OF THE", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC OF", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC", "CD")
Loop
Do While InStr(Cell, "COOK ISLANDS") > 0
Cell.Value = Replace(Cell, "COOK ISLANDS", "CK")
Loop
Do While InStr(Cell, "COSTA RICA") > 0
Cell.Value = Replace(Cell, "COSTA RICA", "CR")
Loop
Do While InStr(Cell, "COTE D'IVOIRE") > 0
Cell.Value = Replace(Cell, "COTE D'IVOIRE", "CI")
Loop
Do While InStr(Cell, "COTE DIVOIRE") > 0
Cell.Value = Replace(Cell, "COTE DIVOIRE", "CI")
Loop
Do While InStr(Cell, "CROATIA") > 0
Cell.Value = Replace(Cell, "CROATIA", "HR")
Loop
Do While InStr(Cell, "CROATIA (LOCAL NAME: HRVATSKA)") > 0
Cell.Value = Replace(Cell, "CROATIA (LOCAL NAME: HRVATSKA)", "HR")
Loop
Do While InStr(Cell, "CUBA") > 0
Cell.Value = Replace(Cell, "CUBA", "CU")
Loop
Do While InStr(Cell, "CYPRUS") > 0
Cell.Value = Replace(Cell, "CYPRUS", "CY")
Loop
Do While InStr(Cell, "CZECH REP.") > 0
Cell.Value = Replace(Cell, "CZECH REP.", "CZ")
Loop
Do While InStr(Cell, "CZECH REPUBLIC") > 0
Cell.Value = Replace(Cell, "CZECH REPUBLIC", "CZ")
Loop
Do While InStr(Cell, "DENMARK") > 0
Cell.Value = Replace(Cell, "DENMARK", "DK")
Loop
Do While InStr(Cell, "DJIBOUTI") > 0
Cell.Value = Replace(Cell, "DJIBOUTI", "DJ")
Loop
Do While InStr(Cell, "DOMINICA") > 0
Cell.Value = Replace(Cell, "DOMINICA", "DM")
Loop
Do While InStr(Cell, "DOMINICAN REP.") > 0
Cell.Value = Replace(Cell, "DOMINICAN REP.", "DO")
Loop
Do While InStr(Cell, "DOMINICAN REPUBLIC") > 0
Cell.Value = Replace(Cell, "DOMINICAN REPUBLIC", "DO")
Loop
Do While InStr(Cell, "ECUADOR") > 0
Cell.Value = Replace(Cell, "ECUADOR", "EC")
Loop
Do While InStr(Cell, "EGYPT") > 0
Cell.Value = Replace(Cell, "EGYPT", "EG")
Loop
Do While InStr(Cell, "EL SALVADOR") > 0
Cell.Value = Replace(Cell, "EL SALVADOR", "SV")
Loop
Do While InStr(Cell, "EQUATORIAL GUINEA") > 0
Cell.Value = Replace(Cell, "EQUATORIAL GUINEA", "GQ")
Loop
Do While InStr(Cell, "ERITREA") > 0
Cell.Value = Replace(Cell, "ERITREA", "ER")
Loop
Do While InStr(Cell, "ESTONIA") > 0
Cell.Value = Replace(Cell, "ESTONIA", "EE")
Loop
Do While InStr(Cell, "ETHIOPIA") > 0
Cell.Value = Replace(Cell, "ETHIOPIA", "ET")
Loop
Do While InStr(Cell, "FALKLAND ISLANDS (MALVINAS)") > 0
Cell.Value = Replace(Cell, "FALKLAND ISLANDS (MALVINAS)", "FK")
Loop
Do While InStr(Cell, "MALVINAS") > 0
Cell.Value = Replace(Cell, "MALVINAS", "FK")
Loop
Do While InStr(Cell, "FALKLAND ISLANDS") > 0
Cell.Value = Replace(Cell, "FALKLAND ISLANDS", "FK")
Loop
Do While InStr(Cell, "FAROE ISLANDS") > 0
Cell.Value = Replace(Cell, "FAROE ISLANDS", "FO")
Loop
Do While InStr(Cell, "FIJI") > 0
Cell.Value = Replace(Cell, "FIJI", "FJ")
Loop
Do While InStr(Cell, "FINLAND") > 0
Cell.Value = Replace(Cell, "FINLAND", "FI")
Loop
Do While InStr(Cell, "FRANCE") > 0
Cell.Value = Replace(Cell, "FRANCE", "FR")
Loop
Do While InStr(Cell, "FRENCH GUIANA") > 0
Cell.Value = Replace(Cell, "FRENCH GUIANA", "GF")
Loop
Do While InStr(Cell, "FRENCH SOUTHERN TERRITORIES") > 0
Cell.Value = Replace(Cell, "FRENCH SOUTHERN TERRITORIES", "TF")
Loop
Do While InStr(Cell, "GABON") > 0
Cell.Value = Replace(Cell, "GABON", "GA")
Loop
Do While InStr(Cell, "GAMBIA") > 0
Cell.Value = Replace(Cell, "GAMBIA", "GM")
Loop
Do While InStr(Cell, "GEORGIA") > 0
Cell.Value = Replace(Cell, "GEORGIA", "GE")
Loop
Do While InStr(Cell, "GERMANY") > 0
Cell.Value = Replace(Cell, "GERMANY", "DE")
Loop
Do While InStr(Cell, "GHANA") > 0
Cell.Value = Replace(Cell, "GHANA", "GH")
Loop
Do While InStr(Cell, "GIBRALTAR") > 0
Cell.Value = Replace(Cell, "GIBRALTAR", "GI")
Loop
Do While InStr(Cell, "GREAT BRITAIN") > 0
Cell.Value = Replace(Cell, "GREAT BRITAIN", "GB")
Loop
Do While InStr(Cell, "GREECE") > 0
Cell.Value = Replace(Cell, "GREECE", "GR")
Loop
Do While InStr(Cell, "GREENLAND") > 0
Cell.Value = Replace(Cell, "GREENLAND", "GL")
Loop
Do While InStr(Cell, "GRENADA") > 0
Cell.Value = Replace(Cell, "GRENADA", "GD")
Loop
Do While InStr(Cell, "GUADELOUPE (FRENCH)") > 0
Cell.Value = Replace(Cell, "GUADELOUPE (FRENCH)", "GP")
Loop
Do While InStr(Cell, "GUADELOUPE") > 0
Cell.Value = Replace(Cell, "GUADELOUPE", "GP")
Loop
Do While InStr(Cell, "GUAM (USA)") > 0
Cell.Value = Replace(Cell, "GUAM (USA)", "GU")
Loop
Do While InStr(Cell, "GUAM") > 0
Cell.Value = Replace(Cell, "GUAM", "GU")
Loop
Do While InStr(Cell, "GUATEMALA") > 0
Cell.Value = Replace(Cell, "GUATEMALA", "GT")
Loop
Do While InStr(Cell, "GUERNSEY") > 0
Cell.Value = Replace(Cell, "GUERNSEY", "GG")
Loop
Do While InStr(Cell, "GUINEA") > 0
Cell.Value = Replace(Cell, "GUINEA", "GN")
Loop
Do While InStr(Cell, "GUINEA BISSAU") > 0
Cell.Value = Replace(Cell, "GUINEA BISSAU", "GW")
Loop
Do While InStr(Cell, "GUYANA") > 0
Cell.Value = Replace(Cell, "GUYANA", "GY")
Loop
Do While InStr(Cell, "HAITI") > 0
Cell.Value = Replace(Cell, "HAITI", "HT")
Loop
Do While InStr(Cell, "HEARD ISLAND AND MCDONALD ISLANDS") > 0
Cell.Value = Replace(Cell, "HEARD ISLAND AND MCDONALD ISLANDS", "HM")
Loop
Do While InStr(Cell, "HONDURAS") > 0
Cell.Value = Replace(Cell, "HONDURAS", "HN")
Loop
Do While InStr(Cell, "HONG KONG") > 0
Cell.Value = Replace(Cell, "HONG KONG", "HK")
Loop
Do While InStr(Cell, "HUNGARY") > 0
Cell.Value = Replace(Cell, "HUNGARY", "HU")
Loop
Do While InStr(Cell, "ICELAND") > 0
Cell.Value = Replace(Cell, "ICELAND", "IS")
Loop
Do While InStr(Cell, "INDIA") > 0
Cell.Value = Replace(Cell, "INDIA", "IN")
Loop
Do While InStr(Cell, "INDONESIA") > 0
Cell.Value = Replace(Cell, "INDONESIA", "ID")
Loop
Do While InStr(Cell, "IRAN") > 0
Cell.Value = Replace(Cell, "IRAN", "IR")
Loop
Do While InStr(Cell, "IRAQ") > 0
Cell.Value = Replace(Cell, "IRAQ", "IQ")
Loop
Do While InStr(Cell, "IRELAND") > 0
Cell.Value = Replace(Cell, "IRELAND", "IE")
Loop
Do While InStr(Cell, "ISLE OF MAN") > 0
Cell.Value = Replace(Cell, "ISLE OF MAN", "IM")
Loop
Do While InStr(Cell, "ISRAEL") > 0
Cell.Value = Replace(Cell, "ISRAEL", "IL")
Loop
Do While InStr(Cell, "ITALY") > 0
Cell.Value = Replace(Cell, "ITALY", "IT")
Loop
Do While InStr(Cell, "IVORY COAST") > 0
Cell.Value = Replace(Cell, "IVORY COAST", "CI")
Loop
Do While InStr(Cell, "JAMAICA") > 0
Cell.Value = Replace(Cell, "JAMAICA", "JM")
Loop
Do While InStr(Cell, "JAPAN") > 0
Cell.Value = Replace(Cell, "JAPAN", "JP")
Loop
Do While InStr(Cell, "JERSEY") > 0
Cell.Value = Replace(Cell, "JERSEY", "JE")
Loop
Do While InStr(Cell, "JERSEY, CHANNEL ISLANDS") > 0
Cell.Value = Replace(Cell, "JERSEY, CHANNEL ISLANDS", "JE")
Loop
Do While InStr(Cell, "CHANNEL ISLANDS JERSEY") > 0
Cell.Value = Replace(Cell, "CHANNEL ISLANDS JERSEY", "JE")
Loop
Do While InStr(Cell, "JORDAN") > 0
Cell.Value = Replace(Cell, "JORDAN", "JO")
Loop
Do While InStr(Cell, "KAZAKHSTAN") > 0
Cell.Value = Replace(Cell, "KAZAKHSTAN", "KZ")
Loop
Do While InStr(Cell, "KENYA") > 0
Cell.Value = Replace(Cell, "KENYA", "KE")
Loop
Do While InStr(Cell, "KIRIBATI") > 0
Cell.Value = Replace(Cell, "KIRIBATI", "KI")
Loop
Do While InStr(Cell, "KOREA-NORTH") > 0
Cell.Value = Replace(Cell, "KOREA-NORTH", "KP")
Loop
Do While InStr(Cell, "KOREA NORTH") > 0
Cell.Value = Replace(Cell, "KOREA NORTH", "KP")
Loop
Do While InStr(Cell, "NORTH KOREA") > 0
Cell.Value = Replace(Cell, "NORTH KOREA", "KP")
Loop
Do While InStr(Cell, "KOREA") > 0
Cell.Value = Replace(Cell, "KOREA", "KR")
Loop
Do While InStr(Cell, "KOREA-SOUTH") > 0
Cell.Value = Replace(Cell, "KOREA-SOUTH", "KR")
Loop
Do While InStr(Cell, "KOREA SOUTH") > 0
Cell.Value = Replace(Cell, "KOREA SOUTH", "KR")
Loop
Do While InStr(Cell, "SOUTH KOREA") > 0
Cell.Value = Replace(Cell, "SOUTH KOREA", "KR")
Loop
Do While InStr(Cell, "KUWAIT") > 0
Cell.Value = Replace(Cell, "KUWAIT", "KW")
Loop
Do While InStr(Cell, "KYRGYZSTAN") > 0
Cell.Value = Replace(Cell, "KYRGYZSTAN", "KG")
Loop
Do While InStr(Cell, "LAOS") > 0
Cell.Value = Replace(Cell, "LAOS", "LA")
Loop
Do While InStr(Cell, "LATVIA") > 0
Cell.Value = Replace(Cell, "LATVIA", "LV")
Loop
Do While InStr(Cell, "LEBANON") > 0
Cell.Value = Replace(Cell, "LEBANON", "LB")
Loop
Do While InStr(Cell, "LESOTHO") > 0
Cell.Value = Replace(Cell, "LESOTHO", "LS")
Loop
Do While InStr(Cell, "LIBERIA") > 0
Cell.Value = Replace(Cell, "LIBERIA", "LR")
Loop
Do While InStr(Cell, "LIBYA") > 0
Cell.Value = Replace(Cell, "LIBYA", "LY")
Loop
Do While InStr(Cell, "LIECHTENSTEIN") > 0
Cell.Value = Replace(Cell, "LIECHTENSTEIN", "LI")
Loop
Do While InStr(Cell, "LITHUANIA") > 0
Cell.Value = Replace(Cell, "LITHUANIA", "LT")
Loop
Do While InStr(Cell, "LUXEMBOURG") > 0
Cell.Value = Replace(Cell, "LUXEMBOURG", "LU")
Loop
Do While InStr(Cell, "MACAU") > 0
Cell.Value = Replace(Cell, "MACAU", "MO")
Loop
Do While InStr(Cell, "MACEDONIA") > 0
Cell.Value = Replace(Cell, "MACEDONIA", "MK")
Loop
Do While InStr(Cell, "MADAGASCAR") > 0
Cell.Value = Replace(Cell, "MADAGASCAR", "MG")
Loop
Do While InStr(Cell, "MALAWI") > 0
Cell.Value = Replace(Cell, "MALAWI", "MW")
Loop
Do While InStr(Cell, "MALAYSIA") > 0
Cell.Value = Replace(Cell, "MALAYSIA", "MY")
Loop
Do While InStr(Cell, "MALDIVES") > 0
Cell.Value = Replace(Cell, "MALDIVES", "MV")
Loop
Do While InStr(Cell, "MALI") > 0
Cell.Value = Replace(Cell, "MALI", "ML")
Loop
Do While InStr(Cell, "MALTA") > 0
Cell.Value = Replace(Cell, "MALTA", "MT")
Loop
Do While InStr(Cell, "MARSHALL ISLANDS") > 0
Cell.Value = Replace(Cell, "MARSHALL ISLANDS", "MH")
Loop
Do While InStr(Cell, "MARTINIQUE (FRENCH)") > 0
Cell.Value = Replace(Cell, "MARTINIQUE (FRENCH)", "MQ")
Loop
Do While InStr(Cell, "MARTINIQUE") > 0
Cell.Value = Replace(Cell, "MARTINIQUE", "MQ")
Loop
Do While InStr(Cell, "MAURITANIA") > 0
Cell.Value = Replace(Cell, "MAURITANIA", "MR")
Loop
Do While InStr(Cell, "MAURITIUS") > 0
Cell.Value = Replace(Cell, "MAURITIUS", "MU")
Loop
Do While InStr(Cell, "MAYOTTE") > 0
Cell.Value = Replace(Cell, "MAYOTTE", "YT")
Loop
Do While InStr(Cell, "MEXICO") > 0
Cell.Value = Replace(Cell, "MEXICO", "MX")
Loop
Do While InStr(Cell, "MICRONESIA") > 0
Cell.Value = Replace(Cell, "MICRONESIA", "FM")
Loop
Do While InStr(Cell, "MOLDOVA") > 0
Cell.Value = Replace(Cell, "MOLDOVA", "MD")
Loop
Do While InStr(Cell, "MONACO") > 0
Cell.Value = Replace(Cell, "MONACO", "MC")
Loop
Do While InStr(Cell, "MONGOLIA") > 0
Cell.Value = Replace(Cell, "MONGOLIA", "MN")
Loop
Do While InStr(Cell, "MONTENEGRO") > 0
Cell.Value = Replace(Cell, "MONTENEGRO", "ME")
Loop
Do While InStr(Cell, "MONTSERRAT") > 0
Cell.Value = Replace(Cell, "MONTSERRAT", "MS")
Loop
Do While InStr(Cell, "MOROCCO") > 0
Cell.Value = Replace(Cell, "MOROCCO", "MA")
Loop
Do While InStr(Cell, "MOZAMBIQUE") > 0
Cell.Value = Replace(Cell, "MOZAMBIQUE", "MZ")
Loop
Do While InStr(Cell, "MYANMAR") > 0
Cell.Value = Replace(Cell, "MYANMAR", "MM")
Loop
Do While InStr(Cell, "NAMIBIA") > 0
Cell.Value = Replace(Cell, "NAMIBIA", "NA")
Loop
Do While InStr(Cell, "NAURU") > 0
Cell.Value = Replace(Cell, "NAURU", "NR")
Loop
Do While InStr(Cell, "NEPAL") > 0
Cell.Value = Replace(Cell, "NEPAL", "NP")
Loop
Do While InStr(Cell, "NETHERLANDS") > 0
Cell.Value = Replace(Cell, "NETHERLANDS", "NL")
Loop
Do While InStr(Cell, "THE NETHERLANDS") > 0
Cell.Value = Replace(Cell, "THE NETHERLANDS", "NL")
Loop
Do While InStr(Cell, "NETHERLANDS ANTILLES") > 0
Cell.Value = Replace(Cell, "NETHERLANDS ANTILLES", "AN")
Loop
Do While InStr(Cell, "CURACAO") > 0
Cell.Value = Replace(Cell, "CURACAO", "AN")
Loop
Do While InStr(Cell, "SAINT MAARTEN") > 0
Cell.Value = Replace(Cell, "SAINT MAARTEN", "AN")
Loop
Do While InStr(Cell, "ST MAARTEN") > 0
Cell.Value = Replace(Cell, "ST MAARTEN", "AN")
Loop
Do While InStr(Cell, "ST. MAARTEN") > 0
Cell.Value = Replace(Cell, "ST. MAARTEN", "AN")
Loop
Do While InStr(Cell, "SAINT EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "SAINT EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "ST EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "ST EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "ST. EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "ST. EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "NEW CALEDONIA (FRENCH)") > 0
Cell.Value = Replace(Cell, "NEW CALEDONIA (FRENCH)", "NC")
Loop
Do While InStr(Cell, "NEW CALEDONIA") > 0
Cell.Value = Replace(Cell, "NEW CALEDONIA", "NC")
Loop
Do While InStr(Cell, "NEW ZEALAND") > 0
Cell.Value = Replace(Cell, "NEW ZEALAND", "NZ")
Loop
Do While InStr(Cell, "NICARAGUA") > 0
Cell.Value = Replace(Cell, "NICARAGUA", "NI")
Loop
Do While InStr(Cell, "NIGER") > 0
Cell.Value = Replace(Cell, "NIGER", "NE")
Loop
Do While InStr(Cell, "NIGERIA") > 0
Cell.Value = Replace(Cell, "NIGERIA", "NG")
Loop
Do While InStr(Cell, "NIUE") > 0
Cell.Value = Replace(Cell, "NIUE", "NU")
Loop
Do While InStr(Cell, "NORFOLK ISLAND") > 0
Cell.Value = Replace(Cell, "NORFOLK ISLAND", "NF")
Loop
Do While InStr(Cell, "NORTHERN MARIANA ISLANDS") > 0
Cell.Value = Replace(Cell, "NORTHERN MARIANA ISLANDS", "MP")
Loop
Do While InStr(Cell, "NORWAY") > 0
Cell.Value = Replace(Cell, "NORWAY", "NO")
Loop
Do While InStr(Cell, "OMAN") > 0
Cell.Value = Replace(Cell, "OMAN", "OM")
Loop
Do While InStr(Cell, "PAKISTAN") > 0
Cell.Value = Replace(Cell, "PAKISTAN", "PK")
Loop
Do While InStr(Cell, "PALAU") > 0
Cell.Value = Replace(Cell, "PALAU", "PW")
Loop
Do While InStr(Cell, "PALESTINE") > 0
Cell.Value = Replace(Cell, "PALESTINE", "PS")
Loop
Do While InStr(Cell, "PALESTINE, STATE OF") > 0
Cell.Value = Replace(Cell, "PALESTINE, STATE OF", "PS")
Loop
Do While InStr(Cell, "STATE OF PALESTINE") > 0
Cell.Value = Replace(Cell, "STATE OF PALESTINE", "PS")
Loop
Do While InStr(Cell, "PANAMA") > 0
Cell.Value = Replace(Cell, "PANAMA", "PA")
Loop
Do While InStr(Cell, "PAPUA NEW GUINEA") > 0
Cell.Value = Replace(Cell, "PAPUA NEW GUINEA", "PG")
Loop
Do While InStr(Cell, "PARAGUAY") > 0
Cell.Value = Replace(Cell, "PARAGUAY", "PY")
Loop
Do While InStr(Cell, "PERU") > 0
Cell.Value = Replace(Cell, "PERU", "PE")
Loop
Do While InStr(Cell, "PHILIPPINES") > 0
Cell.Value = Replace(Cell, "PHILIPPINES", "PH")
Loop
Do While InStr(Cell, "PITCAIRN ISLAND") > 0
Cell.Value = Replace(Cell, "PITCAIRN ISLAND", "PN")
Loop
Do While InStr(Cell, "POLAND") > 0
Cell.Value = Replace(Cell, "POLAND", "PL")
Loop
Do While InStr(Cell, "POLYNESIA (FRENCH)") > 0
Cell.Value = Replace(Cell, "POLYNESIA (FRENCH)", "PF")
Loop
Do While InStr(Cell, "POLYNESIA") > 0
Cell.Value = Replace(Cell, "POLYNESIA", "PF")
Loop
Do While InStr(Cell, "FRENCH POLYNESIA") > 0
Cell.Value = Replace(Cell, "FRENCH POLYNESIA", "PF")
Loop
Do While InStr(Cell, "PORTUGAL") > 0
Cell.Value = Replace(Cell, "PORTUGAL", "PT")
Loop
Do While InStr(Cell, "PUERTO RICO") > 0
Cell.Value = Replace(Cell, "PUERTO RICO", "PR")
Loop
Do While InStr(Cell, "QATAR") > 0
Cell.Value = Replace(Cell, "QATAR", "QA")
Loop
Do While InStr(Cell, "REUNION (FRENCH)") > 0
Cell.Value = Replace(Cell, "REUNION (FRENCH)", "RE")
Loop
Do While InStr(Cell, "REUNION") > 0
Cell.Value = Replace(Cell, "REUNION", "RE")
Loop
Do While InStr(Cell, "FRENCH REUNION") > 0
Cell.Value = Replace(Cell, "FRENCH REUNION", "RE")
Loop
Do While InStr(Cell, "ROMANIA") > 0
Cell.Value = Replace(Cell, "ROMANIA", "RO")
Loop
Do While InStr(Cell, "RUSSIA") > 0
Cell.Value = Replace(Cell, "RUSSIA", "RU")
Loop
Do While InStr(Cell, "RUSSIAN FEDERATION") > 0
Cell.Value = Replace(Cell, "RUSSIAN FEDERATION", "RU")
Loop
Do While InStr(Cell, "RWANDA") > 0
Cell.Value = Replace(Cell, "RWANDA", "RW")
Loop
Do While InStr(Cell, "SAINT HELENA") > 0
Cell.Value = Replace(Cell, "SAINT HELENA", "SH")
Loop
Do While InStr(Cell, "SAINT KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "SAINT KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "ST KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "ST KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "ST. KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "ST. KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "SAINT LUCIA") > 0
Cell.Value = Replace(Cell, "SAINT LUCIA", "LC")
Loop
Do While InStr(Cell, "ST LUCIA") > 0
Cell.Value = Replace(Cell, "ST LUCIA", "LC")
Loop
Do While InStr(Cell, "ST. LUCIA") > 0
Cell.Value = Replace(Cell, "ST. LUCIA", "LC")
Loop
Do While InStr(Cell, "SAINT PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "SAINT PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "ST PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "ST PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "ST. PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "ST. PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "SAINT VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "SAINT VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "ST VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "ST VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "ST. VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "ST. VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "SAMOA") > 0
Cell.Value = Replace(Cell, "SAMOA", "WS")
Loop
Do While InStr(Cell, "SAN MARINO") > 0
Cell.Value = Replace(Cell, "SAN MARINO", "SM")
Loop
Do While InStr(Cell, "SAO TOME AND PRINCIPE") > 0
Cell.Value = Replace(Cell, "SAO TOME AND PRINCIPE", "ST")
Loop
Do While InStr(Cell, "SAUDI ARABIA") > 0
Cell.Value = Replace(Cell, "SAUDI ARABIA", "SA")
Loop
Do While InStr(Cell, "SENEGAL") > 0
Cell.Value = Replace(Cell, "SENEGAL", "SN")
Loop
Do While InStr(Cell, "SERBIA") > 0
Cell.Value = Replace(Cell, "SERBIA", "RS")
Loop
Do While InStr(Cell, "SEYCHELLES") > 0
Cell.Value = Replace(Cell, "SEYCHELLES", "SC")
Loop
Do While InStr(Cell, "SIERRA LEONE") > 0
Cell.Value = Replace(Cell, "SIERRA LEONE", "SL")
Loop
Do While InStr(Cell, "SINGAPORE") > 0
Cell.Value = Replace(Cell, "SINGAPORE", "SG")
Loop
Do While InStr(Cell, "SLOVAKIA") > 0
Cell.Value = Replace(Cell, "SLOVAKIA", "SK")
Loop
Do While InStr(Cell, "SLOVENIA") > 0
Cell.Value = Replace(Cell, "SLOVENIA", "SI")
Loop
Do While InStr(Cell, "SOLOMON ISLANDS") > 0
Cell.Value = Replace(Cell, "SOLOMON ISLANDS", "SB")
Loop
Do While InStr(Cell, "SOMALIA") > 0
Cell.Value = Replace(Cell, "SOMALIA", "SO")
Loop
Do While InStr(Cell, "SOUTH AFRICA") > 0
Cell.Value = Replace(Cell, "SOUTH AFRICA", "ZA")
Loop
Do While InStr(Cell, "SOUTH GEORGIA & SOUTH SANDWICH ISLANDS") > 0
Cell.Value = Replace(Cell, "SOUTH GEORGIA & SOUTH SANDWICH ISLANDS", "GS")
Loop
Do While InStr(Cell, "SOUTH SUDAN") > 0
Cell.Value = Replace(Cell, "SOUTH SUDAN", "SS")
Loop
Do While InStr(Cell, "SPAIN") > 0
Cell.Value = Replace(Cell, "SPAIN", "ES")
Loop
Do While InStr(Cell, "SRI LANKA") > 0
Cell.Value = Replace(Cell, "SRI LANKA", "LK")
Loop
Do While InStr(Cell, "SUDAN") > 0
Cell.Value = Replace(Cell, "SUDAN", "SD")
Loop
Do While InStr(Cell, "SURINAME") > 0
Cell.Value = Replace(Cell, "SURINAME", "SR")
Loop
Do While InStr(Cell, "SVALBARD AND JAN MAYEN ISLANDS") > 0
Cell.Value = Replace(Cell, "SVALBARD AND JAN MAYEN ISLANDS", "SJ")
Loop
Do While InStr(Cell, "SWAZILAND") > 0
Cell.Value = Replace(Cell, "SWAZILAND", "SZ")
Loop
Do While InStr(Cell, "SWEDEN") > 0
Cell.Value = Replace(Cell, "SWEDEN", "SE")
Loop
Do While InStr(Cell, "SWITZERLAND") > 0
Cell.Value = Replace(Cell, "SWITZERLAND", "CH")
Loop
Do While InStr(Cell, "SYRIA") > 0
Cell.Value = Replace(Cell, "SYRIA", "SY")
Loop
Do While InStr(Cell, "TAIWAN") > 0
Cell.Value = Replace(Cell, "TAIWAN", "TW")
Loop
Do While InStr(Cell, "TAJIKISTAN") > 0
Cell.Value = Replace(Cell, "TAJIKISTAN", "TJ")
Loop
Do While InStr(Cell, "TANZANIA") > 0
Cell.Value = Replace(Cell, "TANZANIA", "TZ")
Loop
Do While InStr(Cell, "TANZANIA, UNITED REPUBLIC") > 0
Cell.Value = Replace(Cell, "TANZANIA, UNITED REPUBLIC", "TZ")
Loop
Do While InStr(Cell, "TANZANIA, UNITED REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "TANZANIA, UNITED REPUBLIC OF", "TZ")
Loop
Do While InStr(Cell, "UNITED REPUBLIC OF TANZANIA") > 0
Cell.Value = Replace(Cell, "UNITED REPUBLIC OF TANZANIA", "TZ")
Loop
Do While InStr(Cell, "THAILAND") > 0
Cell.Value = Replace(Cell, "THAILAND", "TH")
Loop
Do While InStr(Cell, "TOGO") > 0
Cell.Value = Replace(Cell, "TOGO", "TG")
Loop
Do While InStr(Cell, "TOKELAU") > 0
Cell.Value = Replace(Cell, "TOKELAU", "TK")
Loop
Do While InStr(Cell, "TONGA") > 0
Cell.Value = Replace(Cell, "TONGA", "TO")
Loop
Do While InStr(Cell, "TRINIDAD AND TOBAGO") > 0
Cell.Value = Replace(Cell, "TRINIDAD AND TOBAGO", "TT")
Loop
Do While InStr(Cell, "TUNISIA") > 0
Cell.Value = Replace(Cell, "TUNISIA", "TN")
Loop
Do While InStr(Cell, "TURKEY") > 0
Cell.Value = Replace(Cell, "TURKEY", "TR")
Loop
Do While InStr(Cell, "TURKMENISTAN") > 0
Cell.Value = Replace(Cell, "TURKMENISTAN", "TM")
Loop
Do While InStr(Cell, "TURKS AND CAICOS ISLANDS") > 0
Cell.Value = Replace(Cell, "TURKS AND CAICOS ISLANDS", "TC")
Loop
Do While InStr(Cell, "TUVALU") > 0
Cell.Value = Replace(Cell, "TUVALU", "TV")
Loop
Do While InStr(Cell, "U.K.") > 0
Cell.Value = Replace(Cell, "U.K.", "GB")
Loop
Do While InStr(Cell, "UK") > 0
Cell.Value = Replace(Cell, "UK", "GB")
Loop
Do While InStr(Cell, "UNITED KINGDOM") > 0
Cell.Value = Replace(Cell, "UNITED KINGDOM", "GB")
Loop
Do While InStr(Cell, "UGANDA") > 0
Cell.Value = Replace(Cell, "UGANDA", "UG")
Loop
Do While InStr(Cell, "UKRAINE") > 0
Cell.Value = Replace(Cell, "UKRAINE", "UA")
Loop
Do While InStr(Cell, "UNITED ARAB EMIRATES") > 0
Cell.Value = Replace(Cell, "UNITED ARAB EMIRATES", "AE")
Loop
Do While InStr(Cell, "URUGUAY") > 0
Cell.Value = Replace(Cell, "URUGUAY", "UY")
Loop
Do While InStr(Cell, "USA") > 0
Cell.Value = Replace(Cell, "USA", "US")
Loop
Do While InStr(Cell, "UNITED STATES") > 0
Cell.Value = Replace(Cell, "UNITED STATES", "US")
Loop
Do While InStr(Cell, "UNITED STATES MINOR OUTLYING ISLANDS") > 0
Cell.Value = Replace(Cell, "UNITED STATES MINOR OUTLYING ISLANDS", "UM")
Loop
Do While InStr(Cell, "USA MINOR OUTLYING ISLANDS") > 0
Cell.Value = Replace(Cell, "USA MINOR OUTLYING ISLANDS", "UM")
Loop
Do While InStr(Cell, "UZBEKISTAN") > 0
Cell.Value = Replace(Cell, "UZBEKISTAN", "UZ")
Loop
Do While InStr(Cell, "VANUATU") > 0
Cell.Value = Replace(Cell, "VANUATU", "VU")
Loop
Do While InStr(Cell, "VATICAN") > 0
Cell.Value = Replace(Cell, "VATICAN", "VA")
Loop
Do While InStr(Cell, "VENEZUELA") > 0
Cell.Value = Replace(Cell, "VENEZUELA", "VE")
Loop
Do While InStr(Cell, "VIETNAM") > 0
Cell.Value = Replace(Cell, "VIETNAM", "VN")
Loop
Do While InStr(Cell, "VIRGIN ISLANDS (BRITISH)") > 0
Cell.Value = Replace(Cell, "VIRGIN ISLANDS (BRITISH)", "VG")
Loop
Do While InStr(Cell, "BRITISH VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "BRITISH VIRGIN ISLANDS", "VG")
Loop
Do While InStr(Cell, "VIRGIN ISLANDS (USA)") > 0
Cell.Value = Replace(Cell, "VIRGIN ISLANDS (USA)", "VI")
Loop
Do While InStr(Cell, "USA VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "USA VIRGIN ISLANDS", "VI")
Loop
Do While InStr(Cell, "UNITED STATES VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "UNITED STATES VIRGIN ISLANDS", "VI")
Loop
Do While InStr(Cell, "WALLIS AND FUTUNA ISLANDS") > 0
Cell.Value = Replace(Cell, "WALLIS AND FUTUNA ISLANDS", "WF")
Loop
Do While InStr(Cell, "WESTERN SAHARA") > 0
Cell.Value = Replace(Cell, "WESTERN SAHARA", "EH")
Loop
Do While InStr(Cell, "YEMEN") > 0
Cell.Value = Replace(Cell, "YEMEN", "YE")
Loop
Do While InStr(Cell, "ZAMBIA") > 0
Cell.Value = Replace(Cell, "ZAMBIA", "ZM")
Loop
Do While InStr(Cell, "ZIMBABWE") > 0
Cell.Value = Replace(Cell, "ZIMBABWE", "ZW")
Loop

Next

End Sub

Code:

Sub ChangeCountryBank()

Dim Cell As Object

 For Each Cell In Sheets("Bulk").Range("AE:AE")

Do While InStr(Cell, "AFGHANISTAN") > 0
Cell.Value = Replace(Cell, "AFGHANISTAN", "AF")
Loop
Do While InStr(Cell, "ALBANIA") > 0
Cell.Value = Replace(Cell, "ALBANIA", "AL")
Loop
Do While InStr(Cell, "ALGERIA") > 0
Cell.Value = Replace(Cell, "ALGERIA", "DZ")
Loop
Do While InStr(Cell, "AMERICAN SAMOA") > 0
Cell.Value = Replace(Cell, "AMERICAN SAMOA", "AS")
Loop
Do While InStr(Cell, "ANDORRA") > 0
Cell.Value = Replace(Cell, "ANDORRA", "AD")
Loop
Do While InStr(Cell, "ANGOLA") > 0
Cell.Value = Replace(Cell, "ANGOLA", "AO")
Loop
Do While InStr(Cell, "ANGUILLA") > 0
Cell.Value = Replace(Cell, "ANGUILLA", "AI")
Loop
Do While InStr(Cell, "ANTARCTICA") > 0
Cell.Value = Replace(Cell, "ANTARCTICA", "AQ")
Loop
Do While InStr(Cell, "ANTIGUA AND BARBUDA") > 0
Cell.Value = Replace(Cell, "ANTIGUA AND BARBUDA", "AG")
Loop
Do While InStr(Cell, "ANTIGUA & BARBUDA") > 0
Cell.Value = Replace(Cell, "ANTIGUA & BARBUDA", "AG")
Loop
Do While InStr(Cell, "ARGENTINA") > 0
Cell.Value = Replace(Cell, "ARGENTINA", "AR")
Loop
Do While InStr(Cell, "ARMENIA") > 0
Cell.Value = Replace(Cell, "ARMENIA", "AM")
Loop
Do While InStr(Cell, "ARUBA") > 0
Cell.Value = Replace(Cell, "ARUBA", "AW")
Loop
Do While InStr(Cell, "AUSTRALIA") > 0
Cell.Value = Replace(Cell, "AUSTRALIA", "AU")
Loop
Do While InStr(Cell, "AUSTRIA") > 0
Cell.Value = Replace(Cell, "AUSTRIA", "AT")
Loop
Do While InStr(Cell, "AZERBAIJAN") > 0
Cell.Value = Replace(Cell, "AZERBAIJAN", "AZ")
Loop
Do While InStr(Cell, "BAHAMAS") > 0
Cell.Value = Replace(Cell, "BAHAMAS", "BS")
Loop
Do While InStr(Cell, "BAHRAIN") > 0
Cell.Value = Replace(Cell, "BAHRAIN", "BH")
Loop
Do While InStr(Cell, "BANGLADESH") > 0
Cell.Value = Replace(Cell, "BANGLADESH", "BD")
Loop
Do While InStr(Cell, "BARBADOS") > 0
Cell.Value = Replace(Cell, "BARBADOS", "BB")
Loop
Do While InStr(Cell, "BELARUS") > 0
Cell.Value = Replace(Cell, "BELARUS", "BY")
Loop
Do While InStr(Cell, "BELGIUM") > 0
Cell.Value = Replace(Cell, "BELGIUM", "BE")
Loop
Do While InStr(Cell, "BELIZE") > 0
Cell.Value = Replace(Cell, "BELIZE", "BZ")
Loop
Do While InStr(Cell, "BENIN") > 0
Cell.Value = Replace(Cell, "BENIN", "BJ")
Loop
Do While InStr(Cell, "BERMUDA") > 0
Cell.Value = Replace(Cell, "BERMUDA", "BM")
Loop
Do While InStr(Cell, "BHUTAN") > 0
Cell.Value = Replace(Cell, "BHUTAN", "BT")
Loop
Do While InStr(Cell, "BOLIVIA") > 0
Cell.Value = Replace(Cell, "BOLIVIA", "BO")
Loop
Do While InStr(Cell, "BOSNIA-HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA-HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOSNIA AND HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA AND HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOSNIA & HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA & HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOTSWANA") > 0
Cell.Value = Replace(Cell, "BOTSWANA", "BW")
Loop
Do While InStr(Cell, "BOUVET ISLAND") > 0
Cell.Value = Replace(Cell, "BOUVET ISLAND", "BV")
Loop
Do While InStr(Cell, "BRAZIL") > 0
Cell.Value = Replace(Cell, "BRAZIL", "BR")
Loop
Do While InStr(Cell, "BRITISH INDIAN OCEAN TERRITORY") > 0
Cell.Value = Replace(Cell, "BRITISH INDIAN OCEAN TERRITORY", "IO")
Loop
Do While InStr(Cell, "BRUNEI DARUSSALAM") > 0
Cell.Value = Replace(Cell, "BRUNEI DARUSSALAM", "BN")
Loop
Do While InStr(Cell, "BULGARIA") > 0
Cell.Value = Replace(Cell, "BULGARIA", "BG")
Loop
Do While InStr(Cell, "BURKINA FASO") > 0
Cell.Value = Replace(Cell, "BURKINA FASO", "BF")
Loop
Do While InStr(Cell, "BURUNDI") > 0
Cell.Value = Replace(Cell, "BURUNDI", "BI")
Loop
Do While InStr(Cell, "CAMBODIA") > 0
Cell.Value = Replace(Cell, "CAMBODIA", "KH")
Loop
Do While InStr(Cell, "CAMEROON") > 0
Cell.Value = Replace(Cell, "CAMEROON", "CM")
Loop
Do While InStr(Cell, "CANADA") > 0
Cell.Value = Replace(Cell, "CANADA", "CA")
Loop
Do While InStr(Cell, "CAPE VERDE") > 0
Cell.Value = Replace(Cell, "CAPE VERDE", "CV")
Loop
Do While InStr(Cell, "CAYMAN ISLANDS") > 0
Cell.Value = Replace(Cell, "CAYMAN ISLANDS", "KY")
Loop
Do While InStr(Cell, "CENTRAL AFRICAN REPUBLIC") > 0
Cell.Value = Replace(Cell, "CENTRAL AFRICAN REPUBLIC", "CF")
Loop
Do While InStr(Cell, "CHAD") > 0
Cell.Value = Replace(Cell, "CHAD", "TD")
Loop
Do While InStr(Cell, "CHILE") > 0
Cell.Value = Replace(Cell, "CHILE", "CL")
Loop
Do While InStr(Cell, "CHINA") > 0
Cell.Value = Replace(Cell, "CHINA", "CN")
Loop
Do While InStr(Cell, "CHRISTMAS ISLAND") > 0
Cell.Value = Replace(Cell, "CHRISTMAS ISLAND", "CX")
Loop
Do While InStr(Cell, "COCOS (KEELING) ISLANDS") > 0
Cell.Value = Replace(Cell, "COCOS (KEELING) ISLANDS", "CC")
Loop
Do While InStr(Cell, "COCOS ISLANDS") > 0
Cell.Value = Replace(Cell, "COCOS ISLANDS", "CC")
Loop
Do While InStr(Cell, "COCOS") > 0
Cell.Value = Replace(Cell, "COCOS", "CC")
Loop
Do While InStr(Cell, "KEELING ISLANDS") > 0
Cell.Value = Replace(Cell, "KEELING ISLANDS", "CC")
Loop
Do While InStr(Cell, "COLOMBIA") > 0
Cell.Value = Replace(Cell, "COLOMBIA", "CO")
Loop
Do While InStr(Cell, "COMOROS") > 0
Cell.Value = Replace(Cell, "COMOROS", "KM")
Loop
Do While InStr(Cell, "CONGO") > 0
Cell.Value = Replace(Cell, "CONGO", "CG")
Loop
Do While InStr(Cell, "REPUBLIC OF THE CONGO") > 0
Cell.Value = Replace(Cell, "REPUBLIC OF THE CONGO", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC OF THE") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC OF THE", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC OF", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC", "CG")
Loop
Do While InStr(Cell, "CONGO, DEM. REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, DEM. REPUBLIC", "CD")
Loop
Do While InStr(Cell, "DEMOCRATIC REPUBLIC OF THE CONGO") > 0
Cell.Value = Replace(Cell, "DEMOCRATIC REPUBLIC OF THE CONGO", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC OF THE") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC OF THE", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC OF", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC", "CD")
Loop
Do While InStr(Cell, "COOK ISLANDS") > 0
Cell.Value = Replace(Cell, "COOK ISLANDS", "CK")
Loop
Do While InStr(Cell, "COSTA RICA") > 0
Cell.Value = Replace(Cell, "COSTA RICA", "CR")
Loop
Do While InStr(Cell, "COTE D'IVOIRE") > 0
Cell.Value = Replace(Cell, "COTE D'IVOIRE", "CI")
Loop
Do While InStr(Cell, "COTE DIVOIRE") > 0
Cell.Value = Replace(Cell, "COTE DIVOIRE", "CI")
Loop
Do While InStr(Cell, "CROATIA") > 0
Cell.Value = Replace(Cell, "CROATIA", "HR")
Loop
Do While InStr(Cell, "CROATIA (LOCAL NAME: HRVATSKA)") > 0
Cell.Value = Replace(Cell, "CROATIA (LOCAL NAME: HRVATSKA)", "HR")
Loop
Do While InStr(Cell, "CUBA") > 0
Cell.Value = Replace(Cell, "CUBA", "CU")
Loop
Do While InStr(Cell, "CYPRUS") > 0
Cell.Value = Replace(Cell, "CYPRUS", "CY")
Loop
Do While InStr(Cell, "CZECH REP.") > 0
Cell.Value = Replace(Cell, "CZECH REP.", "CZ")
Loop
Do While InStr(Cell, "CZECH REPUBLIC") > 0
Cell.Value = Replace(Cell, "CZECH REPUBLIC", "CZ")
Loop
Do While InStr(Cell, "DENMARK") > 0
Cell.Value = Replace(Cell, "DENMARK", "DK")
Loop
Do While InStr(Cell, "DJIBOUTI") > 0
Cell.Value = Replace(Cell, "DJIBOUTI", "DJ")
Loop
Do While InStr(Cell, "DOMINICA") > 0
Cell.Value = Replace(Cell, "DOMINICA", "DM")
Loop
Do While InStr(Cell, "DOMINICAN REP.") > 0
Cell.Value = Replace(Cell, "DOMINICAN REP.", "DO")
Loop
Do While InStr(Cell, "DOMINICAN REPUBLIC") > 0
Cell.Value = Replace(Cell, "DOMINICAN REPUBLIC", "DO")
Loop
Do While InStr(Cell, "ECUADOR") > 0
Cell.Value = Replace(Cell, "ECUADOR", "EC")
Loop
Do While InStr(Cell, "EGYPT") > 0
Cell.Value = Replace(Cell, "EGYPT", "EG")
Loop
Do While InStr(Cell, "EL SALVADOR") > 0
Cell.Value = Replace(Cell, "EL SALVADOR", "SV")
Loop
Do While InStr(Cell, "EQUATORIAL GUINEA") > 0
Cell.Value = Replace(Cell, "EQUATORIAL GUINEA", "GQ")
Loop
Do While InStr(Cell, "ERITREA") > 0
Cell.Value = Replace(Cell, "ERITREA", "ER")
Loop
Do While InStr(Cell, "ESTONIA") > 0
Cell.Value = Replace(Cell, "ESTONIA", "EE")
Loop
Do While InStr(Cell, "ETHIOPIA") > 0
Cell.Value = Replace(Cell, "ETHIOPIA", "ET")
Loop
Do While InStr(Cell, "FALKLAND ISLANDS (MALVINAS)") > 0
Cell.Value = Replace(Cell, "FALKLAND ISLANDS (MALVINAS)", "FK")
Loop
Do While InStr(Cell, "MALVINAS") > 0
Cell.Value = Replace(Cell, "MALVINAS", "FK")
Loop
Do While InStr(Cell, "FALKLAND ISLANDS") > 0
Cell.Value = Replace(Cell, "FALKLAND ISLANDS", "FK")
Loop
Do While InStr(Cell, "FAROE ISLANDS") > 0
Cell.Value = Replace(Cell, "FAROE ISLANDS", "FO")
Loop
Do While InStr(Cell, "FIJI") > 0
Cell.Value = Replace(Cell, "FIJI", "FJ")
Loop
Do While InStr(Cell, "FINLAND") > 0
Cell.Value = Replace(Cell, "FINLAND", "FI")
Loop
Do While InStr(Cell, "FRANCE") > 0
Cell.Value = Replace(Cell, "FRANCE", "FR")
Loop
Do While InStr(Cell, "FRENCH GUIANA") > 0
Cell.Value = Replace(Cell, "FRENCH GUIANA", "GF")
Loop
Do While InStr(Cell, "FRENCH SOUTHERN TERRITORIES") > 0
Cell.Value = Replace(Cell, "FRENCH SOUTHERN TERRITORIES", "TF")
Loop
Do While InStr(Cell, "GABON") > 0
Cell.Value = Replace(Cell, "GABON", "GA")
Loop
Do While InStr(Cell, "GAMBIA") > 0
Cell.Value = Replace(Cell, "GAMBIA", "GM")
Loop
Do While InStr(Cell, "GEORGIA") > 0
Cell.Value = Replace(Cell, "GEORGIA", "GE")
Loop
Do While InStr(Cell, "GERMANY") > 0
Cell.Value = Replace(Cell, "GERMANY", "DE")
Loop
Do While InStr(Cell, "GHANA") > 0
Cell.Value = Replace(Cell, "GHANA", "GH")
Loop
Do While InStr(Cell, "GIBRALTAR") > 0
Cell.Value = Replace(Cell, "GIBRALTAR", "GI")
Loop
Do While InStr(Cell, "GREAT BRITAIN") > 0
Cell.Value = Replace(Cell, "GREAT BRITAIN", "GB")
Loop
Do While InStr(Cell, "GREECE") > 0
Cell.Value = Replace(Cell, "GREECE", "GR")
Loop
Do While InStr(Cell, "GREENLAND") > 0
Cell.Value = Replace(Cell, "GREENLAND", "GL")
Loop
Do While InStr(Cell, "GRENADA") > 0
Cell.Value = Replace(Cell, "GRENADA", "GD")
Loop
Do While InStr(Cell, "GUADELOUPE (FRENCH)") > 0
Cell.Value = Replace(Cell, "GUADELOUPE (FRENCH)", "GP")
Loop
Do While InStr(Cell, "GUADELOUPE") > 0
Cell.Value = Replace(Cell, "GUADELOUPE", "GP")
Loop
Do While InStr(Cell, "GUAM (USA)") > 0
Cell.Value = Replace(Cell, "GUAM (USA)", "GU")
Loop
Do While InStr(Cell, "GUAM") > 0
Cell.Value = Replace(Cell, "GUAM", "GU")
Loop
Do While InStr(Cell, "GUATEMALA") > 0
Cell.Value = Replace(Cell, "GUATEMALA", "GT")
Loop
Do While InStr(Cell, "GUERNSEY") > 0
Cell.Value = Replace(Cell, "GUERNSEY", "GG")
Loop
Do While InStr(Cell, "GUINEA") > 0
Cell.Value = Replace(Cell, "GUINEA", "GN")
Loop
Do While InStr(Cell, "GUINEA BISSAU") > 0
Cell.Value = Replace(Cell, "GUINEA BISSAU", "GW")
Loop
Do While InStr(Cell, "GUYANA") > 0
Cell.Value = Replace(Cell, "GUYANA", "GY")
Loop
Do While InStr(Cell, "HAITI") > 0
Cell.Value = Replace(Cell, "HAITI", "HT")
Loop
Do While InStr(Cell, "HEARD ISLAND AND MCDONALD ISLANDS") > 0
Cell.Value = Replace(Cell, "HEARD ISLAND AND MCDONALD ISLANDS", "HM")
Loop
Do While InStr(Cell, "HONDURAS") > 0
Cell.Value = Replace(Cell, "HONDURAS", "HN")
Loop
Do While InStr(Cell, "HONG KONG") > 0
Cell.Value = Replace(Cell, "HONG KONG", "HK")
Loop
Do While InStr(Cell, "HUNGARY") > 0
Cell.Value = Replace(Cell, "HUNGARY", "HU")
Loop
Do While InStr(Cell, "ICELAND") > 0
Cell.Value = Replace(Cell, "ICELAND", "IS")
Loop
Do While InStr(Cell, "INDIA") > 0
Cell.Value = Replace(Cell, "INDIA", "IN")
Loop
Do While InStr(Cell, "INDONESIA") > 0
Cell.Value = Replace(Cell, "INDONESIA", "ID")
Loop
Do While InStr(Cell, "IRAN") > 0
Cell.Value = Replace(Cell, "IRAN", "IR")
Loop
Do While InStr(Cell, "IRAQ") > 0
Cell.Value = Replace(Cell, "IRAQ", "IQ")
Loop
Do While InStr(Cell, "IRELAND") > 0
Cell.Value = Replace(Cell, "IRELAND", "IE")
Loop
Do While InStr(Cell, "ISLE OF MAN") > 0
Cell.Value = Replace(Cell, "ISLE OF MAN", "IM")
Loop
Do While InStr(Cell, "ISRAEL") > 0
Cell.Value = Replace(Cell, "ISRAEL", "IL")
Loop
Do While InStr(Cell, "ITALY") > 0
Cell.Value = Replace(Cell, "ITALY", "IT")
Loop
Do While InStr(Cell, "IVORY COAST") > 0
Cell.Value = Replace(Cell, "IVORY COAST", "CI")
Loop
Do While InStr(Cell, "JAMAICA") > 0
Cell.Value = Replace(Cell, "JAMAICA", "JM")
Loop
Do While InStr(Cell, "JAPAN") > 0
Cell.Value = Replace(Cell, "JAPAN", "JP")
Loop
Do While InStr(Cell, "JERSEY") > 0
Cell.Value = Replace(Cell, "JERSEY", "JE")
Loop
Do While InStr(Cell, "JERSEY, CHANNEL ISLANDS") > 0
Cell.Value = Replace(Cell, "JERSEY, CHANNEL ISLANDS", "JE")
Loop
Do While InStr(Cell, "CHANNEL ISLANDS JERSEY") > 0
Cell.Value = Replace(Cell, "CHANNEL ISLANDS JERSEY", "JE")
Loop
Do While InStr(Cell, "JORDAN") > 0
Cell.Value = Replace(Cell, "JORDAN", "JO")
Loop
Do While InStr(Cell, "KAZAKHSTAN") > 0
Cell.Value = Replace(Cell, "KAZAKHSTAN", "KZ")
Loop
Do While InStr(Cell, "KENYA") > 0
Cell.Value = Replace(Cell, "KENYA", "KE")
Loop
Do While InStr(Cell, "KIRIBATI") > 0
Cell.Value = Replace(Cell, "KIRIBATI", "KI")
Loop
Do While InStr(Cell, "KOREA-NORTH") > 0
Cell.Value = Replace(Cell, "KOREA-NORTH", "KP")
Loop
Do While InStr(Cell, "KOREA NORTH") > 0
Cell.Value = Replace(Cell, "KOREA NORTH", "KP")
Loop
Do While InStr(Cell, "NORTH KOREA") > 0
Cell.Value = Replace(Cell, "NORTH KOREA", "KP")
Loop
Do While InStr(Cell, "KOREA") > 0
Cell.Value = Replace(Cell, "KOREA", "KR")
Loop
Do While InStr(Cell, "KOREA-SOUTH") > 0
Cell.Value = Replace(Cell, "KOREA-SOUTH", "KR")
Loop
Do While InStr(Cell, "KOREA SOUTH") > 0
Cell.Value = Replace(Cell, "KOREA SOUTH", "KR")
Loop
Do While InStr(Cell, "SOUTH KOREA") > 0
Cell.Value = Replace(Cell, "SOUTH KOREA", "KR")
Loop
Do While InStr(Cell, "KUWAIT") > 0
Cell.Value = Replace(Cell, "KUWAIT", "KW")
Loop
Do While InStr(Cell, "KYRGYZSTAN") > 0
Cell.Value = Replace(Cell, "KYRGYZSTAN", "KG")
Loop
Do While InStr(Cell, "LAOS") > 0
Cell.Value = Replace(Cell, "LAOS", "LA")
Loop
Do While InStr(Cell, "LATVIA") > 0
Cell.Value = Replace(Cell, "LATVIA", "LV")
Loop
Do While InStr(Cell, "LEBANON") > 0
Cell.Value = Replace(Cell, "LEBANON", "LB")
Loop
Do While InStr(Cell, "LESOTHO") > 0
Cell.Value = Replace(Cell, "LESOTHO", "LS")
Loop
Do While InStr(Cell, "LIBERIA") > 0
Cell.Value = Replace(Cell, "LIBERIA", "LR")
Loop
Do While InStr(Cell, "LIBYA") > 0
Cell.Value = Replace(Cell, "LIBYA", "LY")
Loop
Do While InStr(Cell, "LIECHTENSTEIN") > 0
Cell.Value = Replace(Cell, "LIECHTENSTEIN", "LI")
Loop
Do While InStr(Cell, "LITHUANIA") > 0
Cell.Value = Replace(Cell, "LITHUANIA", "LT")
Loop
Do While InStr(Cell, "LUXEMBOURG") > 0
Cell.Value = Replace(Cell, "LUXEMBOURG", "LU")
Loop
Do While InStr(Cell, "MACAU") > 0
Cell.Value = Replace(Cell, "MACAU", "MO")
Loop
Do While InStr(Cell, "MACEDONIA") > 0
Cell.Value = Replace(Cell, "MACEDONIA", "MK")
Loop
Do While InStr(Cell, "MADAGASCAR") > 0
Cell.Value = Replace(Cell, "MADAGASCAR", "MG")
Loop
Do While InStr(Cell, "MALAWI") > 0
Cell.Value = Replace(Cell, "MALAWI", "MW")
Loop
Do While InStr(Cell, "MALAYSIA") > 0
Cell.Value = Replace(Cell, "MALAYSIA", "MY")
Loop
Do While InStr(Cell, "MALDIVES") > 0
Cell.Value = Replace(Cell, "MALDIVES", "MV")
Loop
Do While InStr(Cell, "MALI") > 0
Cell.Value = Replace(Cell, "MALI", "ML")
Loop
Do While InStr(Cell, "MALTA") > 0
Cell.Value = Replace(Cell, "MALTA", "MT")
Loop
Do While InStr(Cell, "MARSHALL ISLANDS") > 0
Cell.Value = Replace(Cell, "MARSHALL ISLANDS", "MH")
Loop
Do While InStr(Cell, "MARTINIQUE (FRENCH)") > 0
Cell.Value = Replace(Cell, "MARTINIQUE (FRENCH)", "MQ")
Loop
Do While InStr(Cell, "MARTINIQUE") > 0
Cell.Value = Replace(Cell, "MARTINIQUE", "MQ")
Loop
Do While InStr(Cell, "MAURITANIA") > 0
Cell.Value = Replace(Cell, "MAURITANIA", "MR")
Loop
Do While InStr(Cell, "MAURITIUS") > 0
Cell.Value = Replace(Cell, "MAURITIUS", "MU")
Loop
Do While InStr(Cell, "MAYOTTE") > 0
Cell.Value = Replace(Cell, "MAYOTTE", "YT")
Loop
Do While InStr(Cell, "MEXICO") > 0
Cell.Value = Replace(Cell, "MEXICO", "MX")
Loop
Do While InStr(Cell, "MICRONESIA") > 0
Cell.Value = Replace(Cell, "MICRONESIA", "FM")
Loop
Do While InStr(Cell, "MOLDOVA") > 0
Cell.Value = Replace(Cell, "MOLDOVA", "MD")
Loop
Do While InStr(Cell, "MONACO") > 0
Cell.Value = Replace(Cell, "MONACO", "MC")
Loop
Do While InStr(Cell, "MONGOLIA") > 0
Cell.Value = Replace(Cell, "MONGOLIA", "MN")
Loop
Do While InStr(Cell, "MONTENEGRO") > 0
Cell.Value = Replace(Cell, "MONTENEGRO", "ME")
Loop
Do While InStr(Cell, "MONTSERRAT") > 0
Cell.Value = Replace(Cell, "MONTSERRAT", "MS")
Loop
Do While InStr(Cell, "MOROCCO") > 0
Cell.Value = Replace(Cell, "MOROCCO", "MA")
Loop
Do While InStr(Cell, "MOZAMBIQUE") > 0
Cell.Value = Replace(Cell, "MOZAMBIQUE", "MZ")
Loop
Do While InStr(Cell, "MYANMAR") > 0
Cell.Value = Replace(Cell, "MYANMAR", "MM")
Loop
Do While InStr(Cell, "NAMIBIA") > 0
Cell.Value = Replace(Cell, "NAMIBIA", "NA")
Loop
Do While InStr(Cell, "NAURU") > 0
Cell.Value = Replace(Cell, "NAURU", "NR")
Loop
Do While InStr(Cell, "NEPAL") > 0
Cell.Value = Replace(Cell, "NEPAL", "NP")
Loop
Do While InStr(Cell, "NETHERLANDS") > 0
Cell.Value = Replace(Cell, "NETHERLANDS", "NL")
Loop
Do While InStr(Cell, "THE NETHERLANDS") > 0
Cell.Value = Replace(Cell, "THE NETHERLANDS", "NL")
Loop
Do While InStr(Cell, "NETHERLANDS ANTILLES") > 0
Cell.Value = Replace(Cell, "NETHERLANDS ANTILLES", "AN")
Loop
Do While InStr(Cell, "CURACAO") > 0
Cell.Value = Replace(Cell, "CURACAO", "AN")
Loop
Do While InStr(Cell, "SAINT MAARTEN") > 0
Cell.Value = Replace(Cell, "SAINT MAARTEN", "AN")
Loop
Do While InStr(Cell, "ST MAARTEN") > 0
Cell.Value = Replace(Cell, "ST MAARTEN", "AN")
Loop
Do While InStr(Cell, "ST. MAARTEN") > 0
Cell.Value = Replace(Cell, "ST. MAARTEN", "AN")
Loop
Do While InStr(Cell, "SAINT EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "SAINT EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "ST EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "ST EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "ST. EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "ST. EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "NEW CALEDONIA (FRENCH)") > 0
Cell.Value = Replace(Cell, "NEW CALEDONIA (FRENCH)", "NC")
Loop
Do While InStr(Cell, "NEW CALEDONIA") > 0
Cell.Value = Replace(Cell, "NEW CALEDONIA", "NC")
Loop
Do While InStr(Cell, "NEW ZEALAND") > 0
Cell.Value = Replace(Cell, "NEW ZEALAND", "NZ")
Loop
Do While InStr(Cell, "NICARAGUA") > 0
Cell.Value = Replace(Cell, "NICARAGUA", "NI")
Loop
Do While InStr(Cell, "NIGER") > 0
Cell.Value = Replace(Cell, "NIGER", "NE")
Loop
Do While InStr(Cell, "NIGERIA") > 0
Cell.Value = Replace(Cell, "NIGERIA", "NG")
Loop
Do While InStr(Cell, "NIUE") > 0
Cell.Value = Replace(Cell, "NIUE", "NU")
Loop
Do While InStr(Cell, "NORFOLK ISLAND") > 0
Cell.Value = Replace(Cell, "NORFOLK ISLAND", "NF")
Loop
Do While InStr(Cell, "NORTHERN MARIANA ISLANDS") > 0
Cell.Value = Replace(Cell, "NORTHERN MARIANA ISLANDS", "MP")
Loop
Do While InStr(Cell, "NORWAY") > 0
Cell.Value = Replace(Cell, "NORWAY", "NO")
Loop
Do While InStr(Cell, "OMAN") > 0
Cell.Value = Replace(Cell, "OMAN", "OM")
Loop
Do While InStr(Cell, "PAKISTAN") > 0
Cell.Value = Replace(Cell, "PAKISTAN", "PK")
Loop
Do While InStr(Cell, "PALAU") > 0
Cell.Value = Replace(Cell, "PALAU", "PW")
Loop
Do While InStr(Cell, "PALESTINE") > 0
Cell.Value = Replace(Cell, "PALESTINE", "PS")
Loop
Do While InStr(Cell, "PALESTINE, STATE OF") > 0
Cell.Value = Replace(Cell, "PALESTINE, STATE OF", "PS")
Loop
Do While InStr(Cell, "STATE OF PALESTINE") > 0
Cell.Value = Replace(Cell, "STATE OF PALESTINE", "PS")
Loop
Do While InStr(Cell, "PANAMA") > 0
Cell.Value = Replace(Cell, "PANAMA", "PA")
Loop
Do While InStr(Cell, "PAPUA NEW GUINEA") > 0
Cell.Value = Replace(Cell, "PAPUA NEW GUINEA", "PG")
Loop
Do While InStr(Cell, "PARAGUAY") > 0
Cell.Value = Replace(Cell, "PARAGUAY", "PY")
Loop
Do While InStr(Cell, "PERU") > 0
Cell.Value = Replace(Cell, "PERU", "PE")
Loop
Do While InStr(Cell, "PHILIPPINES") > 0
Cell.Value = Replace(Cell, "PHILIPPINES", "PH")
Loop
Do While InStr(Cell, "PITCAIRN ISLAND") > 0
Cell.Value = Replace(Cell, "PITCAIRN ISLAND", "PN")
Loop
Do While InStr(Cell, "POLAND") > 0
Cell.Value = Replace(Cell, "POLAND", "PL")
Loop
Do While InStr(Cell, "POLYNESIA (FRENCH)") > 0
Cell.Value = Replace(Cell, "POLYNESIA (FRENCH)", "PF")
Loop
Do While InStr(Cell, "POLYNESIA") > 0
Cell.Value = Replace(Cell, "POLYNESIA", "PF")
Loop
Do While InStr(Cell, "FRENCH POLYNESIA") > 0
Cell.Value = Replace(Cell, "FRENCH POLYNESIA", "PF")
Loop
Do While InStr(Cell, "PORTUGAL") > 0
Cell.Value = Replace(Cell, "PORTUGAL", "PT")
Loop
Do While InStr(Cell, "PUERTO RICO") > 0
Cell.Value = Replace(Cell, "PUERTO RICO", "PR")
Loop
Do While InStr(Cell, "QATAR") > 0
Cell.Value = Replace(Cell, "QATAR", "QA")
Loop
Do While InStr(Cell, "REUNION (FRENCH)") > 0
Cell.Value = Replace(Cell, "REUNION (FRENCH)", "RE")
Loop
Do While InStr(Cell, "REUNION") > 0
Cell.Value = Replace(Cell, "REUNION", "RE")
Loop
Do While InStr(Cell, "FRENCH REUNION") > 0
Cell.Value = Replace(Cell, "FRENCH REUNION", "RE")
Loop
Do While InStr(Cell, "ROMANIA") > 0
Cell.Value = Replace(Cell, "ROMANIA", "RO")
Loop
Do While InStr(Cell, "RUSSIA") > 0
Cell.Value = Replace(Cell, "RUSSIA", "RU")
Loop
Do While InStr(Cell, "RUSSIAN FEDERATION") > 0
Cell.Value = Replace(Cell, "RUSSIAN FEDERATION", "RU")
Loop
Do While InStr(Cell, "RWANDA") > 0
Cell.Value = Replace(Cell, "RWANDA", "RW")
Loop
Do While InStr(Cell, "SAINT HELENA") > 0
Cell.Value = Replace(Cell, "SAINT HELENA", "SH")
Loop
Do While InStr(Cell, "SAINT KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "SAINT KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "ST KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "ST KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "ST. KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "ST. KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "SAINT LUCIA") > 0
Cell.Value = Replace(Cell, "SAINT LUCIA", "LC")
Loop
Do While InStr(Cell, "ST LUCIA") > 0
Cell.Value = Replace(Cell, "ST LUCIA", "LC")
Loop
Do While InStr(Cell, "ST. LUCIA") > 0
Cell.Value = Replace(Cell, "ST. LUCIA", "LC")
Loop
Do While InStr(Cell, "SAINT PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "SAINT PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "ST PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "ST PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "ST. PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "ST. PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "SAINT VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "SAINT VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "ST VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "ST VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "ST. VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "ST. VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "SAMOA") > 0
Cell.Value = Replace(Cell, "SAMOA", "WS")
Loop
Do While InStr(Cell, "SAN MARINO") > 0
Cell.Value = Replace(Cell, "SAN MARINO", "SM")
Loop
Do While InStr(Cell, "SAO TOME AND PRINCIPE") > 0
Cell.Value = Replace(Cell, "SAO TOME AND PRINCIPE", "ST")
Loop
Do While InStr(Cell, "SAUDI ARABIA") > 0
Cell.Value = Replace(Cell, "SAUDI ARABIA", "SA")
Loop
Do While InStr(Cell, "SENEGAL") > 0
Cell.Value = Replace(Cell, "SENEGAL", "SN")
Loop
Do While InStr(Cell, "SERBIA") > 0
Cell.Value = Replace(Cell, "SERBIA", "RS")
Loop
Do While InStr(Cell, "SEYCHELLES") > 0
Cell.Value = Replace(Cell, "SEYCHELLES", "SC")
Loop
Do While InStr(Cell, "SIERRA LEONE") > 0
Cell.Value = Replace(Cell, "SIERRA LEONE", "SL")
Loop
Do While InStr(Cell, "SINGAPORE") > 0
Cell.Value = Replace(Cell, "SINGAPORE", "SG")
Loop
Do While InStr(Cell, "SLOVAKIA") > 0
Cell.Value = Replace(Cell, "SLOVAKIA", "SK")
Loop
Do While InStr(Cell, "SLOVENIA") > 0
Cell.Value = Replace(Cell, "SLOVENIA", "SI")
Loop
Do While InStr(Cell, "SOLOMON ISLANDS") > 0
Cell.Value = Replace(Cell, "SOLOMON ISLANDS", "SB")
Loop
Do While InStr(Cell, "SOMALIA") > 0
Cell.Value = Replace(Cell, "SOMALIA", "SO")
Loop
Do While InStr(Cell, "SOUTH AFRICA") > 0
Cell.Value = Replace(Cell, "SOUTH AFRICA", "ZA")
Loop
Do While InStr(Cell, "SOUTH GEORGIA & SOUTH SANDWICH ISLANDS") > 0
Cell.Value = Replace(Cell, "SOUTH GEORGIA & SOUTH SANDWICH ISLANDS", "GS")
Loop
Do While InStr(Cell, "SOUTH SUDAN") > 0
Cell.Value = Replace(Cell, "SOUTH SUDAN", "SS")
Loop
Do While InStr(Cell, "SPAIN") > 0
Cell.Value = Replace(Cell, "SPAIN", "ES")
Loop
Do While InStr(Cell, "SRI LANKA") > 0
Cell.Value = Replace(Cell, "SRI LANKA", "LK")
Loop
Do While InStr(Cell, "SUDAN") > 0
Cell.Value = Replace(Cell, "SUDAN", "SD")
Loop
Do While InStr(Cell, "SURINAME") > 0
Cell.Value = Replace(Cell, "SURINAME", "SR")
Loop
Do While InStr(Cell, "SVALBARD AND JAN MAYEN ISLANDS") > 0
Cell.Value = Replace(Cell, "SVALBARD AND JAN MAYEN ISLANDS", "SJ")
Loop
Do While InStr(Cell, "SWAZILAND") > 0
Cell.Value = Replace(Cell, "SWAZILAND", "SZ")
Loop
Do While InStr(Cell, "SWEDEN") > 0
Cell.Value = Replace(Cell, "SWEDEN", "SE")
Loop
Do While InStr(Cell, "SWITZERLAND") > 0
Cell.Value = Replace(Cell, "SWITZERLAND", "CH")
Loop
Do While InStr(Cell, "SYRIA") > 0
Cell.Value = Replace(Cell, "SYRIA", "SY")
Loop
Do While InStr(Cell, "TAIWAN") > 0
Cell.Value = Replace(Cell, "TAIWAN", "TW")
Loop
Do While InStr(Cell, "TAJIKISTAN") > 0
Cell.Value = Replace(Cell, "TAJIKISTAN", "TJ")
Loop
Do While InStr(Cell, "TANZANIA") > 0
Cell.Value = Replace(Cell, "TANZANIA", "TZ")
Loop
Do While InStr(Cell, "TANZANIA, UNITED REPUBLIC") > 0
Cell.Value = Replace(Cell, "TANZANIA, UNITED REPUBLIC", "TZ")
Loop
Do While InStr(Cell, "TANZANIA, UNITED REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "TANZANIA, UNITED REPUBLIC OF", "TZ")
Loop
Do While InStr(Cell, "UNITED REPUBLIC OF TANZANIA") > 0
Cell.Value = Replace(Cell, "UNITED REPUBLIC OF TANZANIA", "TZ")
Loop
Do While InStr(Cell, "THAILAND") > 0
Cell.Value = Replace(Cell, "THAILAND", "TH")
Loop
Do While InStr(Cell, "TOGO") > 0
Cell.Value = Replace(Cell, "TOGO", "TG")
Loop
Do While InStr(Cell, "TOKELAU") > 0
Cell.Value = Replace(Cell, "TOKELAU", "TK")
Loop
Do While InStr(Cell, "TONGA") > 0
Cell.Value = Replace(Cell, "TONGA", "TO")
Loop
Do While InStr(Cell, "TRINIDAD AND TOBAGO") > 0
Cell.Value = Replace(Cell, "TRINIDAD AND TOBAGO", "TT")
Loop
Do While InStr(Cell, "TUNISIA") > 0
Cell.Value = Replace(Cell, "TUNISIA", "TN")
Loop
Do While InStr(Cell, "TURKEY") > 0
Cell.Value = Replace(Cell, "TURKEY", "TR")
Loop
Do While InStr(Cell, "TURKMENISTAN") > 0
Cell.Value = Replace(Cell, "TURKMENISTAN", "TM")
Loop
Do While InStr(Cell, "TURKS AND CAICOS ISLANDS") > 0
Cell.Value = Replace(Cell, "TURKS AND CAICOS ISLANDS", "TC")
Loop
Do While InStr(Cell, "TUVALU") > 0
Cell.Value = Replace(Cell, "TUVALU", "TV")
Loop
Do While InStr(Cell, "U.K.") > 0
Cell.Value = Replace(Cell, "U.K.", "GB")
Loop
Do While InStr(Cell, "UK") > 0
Cell.Value = Replace(Cell, "UK", "GB")
Loop
Do While InStr(Cell, "UNITED KINGDOM") > 0
Cell.Value = Replace(Cell, "UNITED KINGDOM", "GB")
Loop
Do While InStr(Cell, "UGANDA") > 0
Cell.Value = Replace(Cell, "UGANDA", "UG")
Loop
Do While InStr(Cell, "UKRAINE") > 0
Cell.Value = Replace(Cell, "UKRAINE", "UA")
Loop
Do While InStr(Cell, "UNITED ARAB EMIRATES") > 0
Cell.Value = Replace(Cell, "UNITED ARAB EMIRATES", "AE")
Loop
Do While InStr(Cell, "URUGUAY") > 0
Cell.Value = Replace(Cell, "URUGUAY", "UY")
Loop
Do While InStr(Cell, "USA") > 0
Cell.Value = Replace(Cell, "USA", "US")
Loop
Do While InStr(Cell, "UNITED STATES") > 0
Cell.Value = Replace(Cell, "UNITED STATES", "US")
Loop
Do While InStr(Cell, "UNITED STATES MINOR OUTLYING ISLANDS") > 0
Cell.Value = Replace(Cell, "UNITED STATES MINOR OUTLYING ISLANDS", "UM")
Loop
Do While InStr(Cell, "USA MINOR OUTLYING ISLANDS") > 0
Cell.Value = Replace(Cell, "USA MINOR OUTLYING ISLANDS", "UM")
Loop
Do While InStr(Cell, "UZBEKISTAN") > 0
Cell.Value = Replace(Cell, "UZBEKISTAN", "UZ")
Loop
Do While InStr(Cell, "VANUATU") > 0
Cell.Value = Replace(Cell, "VANUATU", "VU")
Loop
Do While InStr(Cell, "VATICAN") > 0
Cell.Value = Replace(Cell, "VATICAN", "VA")
Loop
Do While InStr(Cell, "VENEZUELA") > 0
Cell.Value = Replace(Cell, "VENEZUELA", "VE")
Loop
Do While InStr(Cell, "VIETNAM") > 0
Cell.Value = Replace(Cell, "VIETNAM", "VN")
Loop
Do While InStr(Cell, "VIRGIN ISLANDS (BRITISH)") > 0
Cell.Value = Replace(Cell, "VIRGIN ISLANDS (BRITISH)", "VG")
Loop
Do While InStr(Cell, "BRITISH VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "BRITISH VIRGIN ISLANDS", "VG")
Loop
Do While InStr(Cell, "VIRGIN ISLANDS (USA)") > 0
Cell.Value = Replace(Cell, "VIRGIN ISLANDS (USA)", "VI")
Loop
Do While InStr(Cell, "USA VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "USA VIRGIN ISLANDS", "VI")
Loop
Do While InStr(Cell, "UNITED STATES VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "UNITED STATES VIRGIN ISLANDS", "VI")
Loop
Do While InStr(Cell, "WALLIS AND FUTUNA ISLANDS") > 0
Cell.Value = Replace(Cell, "WALLIS AND FUTUNA ISLANDS", "WF")
Loop
Do While InStr(Cell, "WESTERN SAHARA") > 0
Cell.Value = Replace(Cell, "WESTERN SAHARA", "EH")
Loop
Do While InStr(Cell, "YEMEN") > 0
Cell.Value = Replace(Cell, "YEMEN", "YE")
Loop
Do While InStr(Cell, "ZAMBIA") > 0
Cell.Value = Replace(Cell, "ZAMBIA", "ZM")
Loop
Do While InStr(Cell, "ZIMBABWE") > 0
Cell.Value = Replace(Cell, "ZIMBABWE", "ZW")
Loop

Next

End Sub

Code:

Sub ChangeCountryIBK()

Dim Cell As Object

 For Each Cell In Sheets("Bulk").Range("AO:AO")

Do While InStr(Cell, "AFGHANISTAN") > 0
Cell.Value = Replace(Cell, "AFGHANISTAN", "AF")
Loop
Do While InStr(Cell, "ALBANIA") > 0
Cell.Value = Replace(Cell, "ALBANIA", "AL")
Loop
Do While InStr(Cell, "ALGERIA") > 0
Cell.Value = Replace(Cell, "ALGERIA", "DZ")
Loop
Do While InStr(Cell, "AMERICAN SAMOA") > 0
Cell.Value = Replace(Cell, "AMERICAN SAMOA", "AS")
Loop
Do While InStr(Cell, "ANDORRA") > 0
Cell.Value = Replace(Cell, "ANDORRA", "AD")
Loop
Do While InStr(Cell, "ANGOLA") > 0
Cell.Value = Replace(Cell, "ANGOLA", "AO")
Loop
Do While InStr(Cell, "ANGUILLA") > 0
Cell.Value = Replace(Cell, "ANGUILLA", "AI")
Loop
Do While InStr(Cell, "ANTARCTICA") > 0
Cell.Value = Replace(Cell, "ANTARCTICA", "AQ")
Loop
Do While InStr(Cell, "ANTIGUA AND BARBUDA") > 0
Cell.Value = Replace(Cell, "ANTIGUA AND BARBUDA", "AG")
Loop
Do While InStr(Cell, "ANTIGUA & BARBUDA") > 0
Cell.Value = Replace(Cell, "ANTIGUA & BARBUDA", "AG")
Loop
Do While InStr(Cell, "ARGENTINA") > 0
Cell.Value = Replace(Cell, "ARGENTINA", "AR")
Loop
Do While InStr(Cell, "ARMENIA") > 0
Cell.Value = Replace(Cell, "ARMENIA", "AM")
Loop
Do While InStr(Cell, "ARUBA") > 0
Cell.Value = Replace(Cell, "ARUBA", "AW")
Loop
Do While InStr(Cell, "AUSTRALIA") > 0
Cell.Value = Replace(Cell, "AUSTRALIA", "AU")
Loop
Do While InStr(Cell, "AUSTRIA") > 0
Cell.Value = Replace(Cell, "AUSTRIA", "AT")
Loop
Do While InStr(Cell, "AZERBAIJAN") > 0
Cell.Value = Replace(Cell, "AZERBAIJAN", "AZ")
Loop
Do While InStr(Cell, "BAHAMAS") > 0
Cell.Value = Replace(Cell, "BAHAMAS", "BS")
Loop
Do While InStr(Cell, "BAHRAIN") > 0
Cell.Value = Replace(Cell, "BAHRAIN", "BH")
Loop
Do While InStr(Cell, "BANGLADESH") > 0
Cell.Value = Replace(Cell, "BANGLADESH", "BD")
Loop
Do While InStr(Cell, "BARBADOS") > 0
Cell.Value = Replace(Cell, "BARBADOS", "BB")
Loop
Do While InStr(Cell, "BELARUS") > 0
Cell.Value = Replace(Cell, "BELARUS", "BY")
Loop
Do While InStr(Cell, "BELGIUM") > 0
Cell.Value = Replace(Cell, "BELGIUM", "BE")
Loop
Do While InStr(Cell, "BELIZE") > 0
Cell.Value = Replace(Cell, "BELIZE", "BZ")
Loop
Do While InStr(Cell, "BENIN") > 0
Cell.Value = Replace(Cell, "BENIN", "BJ")
Loop
Do While InStr(Cell, "BERMUDA") > 0
Cell.Value = Replace(Cell, "BERMUDA", "BM")
Loop
Do While InStr(Cell, "BHUTAN") > 0
Cell.Value = Replace(Cell, "BHUTAN", "BT")
Loop
Do While InStr(Cell, "BOLIVIA") > 0
Cell.Value = Replace(Cell, "BOLIVIA", "BO")
Loop
Do While InStr(Cell, "BOSNIA-HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA-HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOSNIA AND HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA AND HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOSNIA & HERZEGOVINA") > 0
Cell.Value = Replace(Cell, "BOSNIA & HERZEGOVINA", "BA")
Loop
Do While InStr(Cell, "BOTSWANA") > 0
Cell.Value = Replace(Cell, "BOTSWANA", "BW")
Loop
Do While InStr(Cell, "BOUVET ISLAND") > 0
Cell.Value = Replace(Cell, "BOUVET ISLAND", "BV")
Loop
Do While InStr(Cell, "BRAZIL") > 0
Cell.Value = Replace(Cell, "BRAZIL", "BR")
Loop
Do While InStr(Cell, "BRITISH INDIAN OCEAN TERRITORY") > 0
Cell.Value = Replace(Cell, "BRITISH INDIAN OCEAN TERRITORY", "IO")
Loop
Do While InStr(Cell, "BRUNEI DARUSSALAM") > 0
Cell.Value = Replace(Cell, "BRUNEI DARUSSALAM", "BN")
Loop
Do While InStr(Cell, "BULGARIA") > 0
Cell.Value = Replace(Cell, "BULGARIA", "BG")
Loop
Do While InStr(Cell, "BURKINA FASO") > 0
Cell.Value = Replace(Cell, "BURKINA FASO", "BF")
Loop
Do While InStr(Cell, "BURUNDI") > 0
Cell.Value = Replace(Cell, "BURUNDI", "BI")
Loop
Do While InStr(Cell, "CAMBODIA") > 0
Cell.Value = Replace(Cell, "CAMBODIA", "KH")
Loop
Do While InStr(Cell, "CAMEROON") > 0
Cell.Value = Replace(Cell, "CAMEROON", "CM")
Loop
Do While InStr(Cell, "CANADA") > 0
Cell.Value = Replace(Cell, "CANADA", "CA")
Loop
Do While InStr(Cell, "CAPE VERDE") > 0
Cell.Value = Replace(Cell, "CAPE VERDE", "CV")
Loop
Do While InStr(Cell, "CAYMAN ISLANDS") > 0
Cell.Value = Replace(Cell, "CAYMAN ISLANDS", "KY")
Loop
Do While InStr(Cell, "CENTRAL AFRICAN REPUBLIC") > 0
Cell.Value = Replace(Cell, "CENTRAL AFRICAN REPUBLIC", "CF")
Loop
Do While InStr(Cell, "CHAD") > 0
Cell.Value = Replace(Cell, "CHAD", "TD")
Loop
Do While InStr(Cell, "CHILE") > 0
Cell.Value = Replace(Cell, "CHILE", "CL")
Loop
Do While InStr(Cell, "CHINA") > 0
Cell.Value = Replace(Cell, "CHINA", "CN")
Loop
Do While InStr(Cell, "CHRISTMAS ISLAND") > 0
Cell.Value = Replace(Cell, "CHRISTMAS ISLAND", "CX")
Loop
Do While InStr(Cell, "COCOS (KEELING) ISLANDS") > 0
Cell.Value = Replace(Cell, "COCOS (KEELING) ISLANDS", "CC")
Loop
Do While InStr(Cell, "COCOS ISLANDS") > 0
Cell.Value = Replace(Cell, "COCOS ISLANDS", "CC")
Loop
Do While InStr(Cell, "COCOS") > 0
Cell.Value = Replace(Cell, "COCOS", "CC")
Loop
Do While InStr(Cell, "KEELING ISLANDS") > 0
Cell.Value = Replace(Cell, "KEELING ISLANDS", "CC")
Loop
Do While InStr(Cell, "COLOMBIA") > 0
Cell.Value = Replace(Cell, "COLOMBIA", "CO")
Loop
Do While InStr(Cell, "COMOROS") > 0
Cell.Value = Replace(Cell, "COMOROS", "KM")
Loop
Do While InStr(Cell, "CONGO") > 0
Cell.Value = Replace(Cell, "CONGO", "CG")
Loop
Do While InStr(Cell, "REPUBLIC OF THE CONGO") > 0
Cell.Value = Replace(Cell, "REPUBLIC OF THE CONGO", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC OF THE") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC OF THE", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC OF", "CG")
Loop
Do While InStr(Cell, "CONGO, REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, REPUBLIC", "CG")
Loop
Do While InStr(Cell, "CONGO, DEM. REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, DEM. REPUBLIC", "CD")
Loop
Do While InStr(Cell, "DEMOCRATIC REPUBLIC OF THE CONGO") > 0
Cell.Value = Replace(Cell, "DEMOCRATIC REPUBLIC OF THE CONGO", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC OF THE") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC OF THE", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC OF", "CD")
Loop
Do While InStr(Cell, "CONGO, DEMOCRATIC REPUBLIC") > 0
Cell.Value = Replace(Cell, "CONGO, DEMOCRATIC REPUBLIC", "CD")
Loop
Do While InStr(Cell, "COOK ISLANDS") > 0
Cell.Value = Replace(Cell, "COOK ISLANDS", "CK")
Loop
Do While InStr(Cell, "COSTA RICA") > 0
Cell.Value = Replace(Cell, "COSTA RICA", "CR")
Loop
Do While InStr(Cell, "COTE D'IVOIRE") > 0
Cell.Value = Replace(Cell, "COTE D'IVOIRE", "CI")
Loop
Do While InStr(Cell, "COTE DIVOIRE") > 0
Cell.Value = Replace(Cell, "COTE DIVOIRE", "CI")
Loop
Do While InStr(Cell, "CROATIA") > 0
Cell.Value = Replace(Cell, "CROATIA", "HR")
Loop
Do While InStr(Cell, "CROATIA (LOCAL NAME: HRVATSKA)") > 0
Cell.Value = Replace(Cell, "CROATIA (LOCAL NAME: HRVATSKA)", "HR")
Loop
Do While InStr(Cell, "CUBA") > 0
Cell.Value = Replace(Cell, "CUBA", "CU")
Loop
Do While InStr(Cell, "CYPRUS") > 0
Cell.Value = Replace(Cell, "CYPRUS", "CY")
Loop
Do While InStr(Cell, "CZECH REP.") > 0
Cell.Value = Replace(Cell, "CZECH REP.", "CZ")
Loop
Do While InStr(Cell, "CZECH REPUBLIC") > 0
Cell.Value = Replace(Cell, "CZECH REPUBLIC", "CZ")
Loop
Do While InStr(Cell, "DENMARK") > 0
Cell.Value = Replace(Cell, "DENMARK", "DK")
Loop
Do While InStr(Cell, "DJIBOUTI") > 0
Cell.Value = Replace(Cell, "DJIBOUTI", "DJ")
Loop
Do While InStr(Cell, "DOMINICA") > 0
Cell.Value = Replace(Cell, "DOMINICA", "DM")
Loop
Do While InStr(Cell, "DOMINICAN REP.") > 0
Cell.Value = Replace(Cell, "DOMINICAN REP.", "DO")
Loop
Do While InStr(Cell, "DOMINICAN REPUBLIC") > 0
Cell.Value = Replace(Cell, "DOMINICAN REPUBLIC", "DO")
Loop
Do While InStr(Cell, "ECUADOR") > 0
Cell.Value = Replace(Cell, "ECUADOR", "EC")
Loop
Do While InStr(Cell, "EGYPT") > 0
Cell.Value = Replace(Cell, "EGYPT", "EG")
Loop
Do While InStr(Cell, "EL SALVADOR") > 0
Cell.Value = Replace(Cell, "EL SALVADOR", "SV")
Loop
Do While InStr(Cell, "EQUATORIAL GUINEA") > 0
Cell.Value = Replace(Cell, "EQUATORIAL GUINEA", "GQ")
Loop
Do While InStr(Cell, "ERITREA") > 0
Cell.Value = Replace(Cell, "ERITREA", "ER")
Loop
Do While InStr(Cell, "ESTONIA") > 0
Cell.Value = Replace(Cell, "ESTONIA", "EE")
Loop
Do While InStr(Cell, "ETHIOPIA") > 0
Cell.Value = Replace(Cell, "ETHIOPIA", "ET")
Loop
Do While InStr(Cell, "FALKLAND ISLANDS (MALVINAS)") > 0
Cell.Value = Replace(Cell, "FALKLAND ISLANDS (MALVINAS)", "FK")
Loop
Do While InStr(Cell, "MALVINAS") > 0
Cell.Value = Replace(Cell, "MALVINAS", "FK")
Loop
Do While InStr(Cell, "FALKLAND ISLANDS") > 0
Cell.Value = Replace(Cell, "FALKLAND ISLANDS", "FK")
Loop
Do While InStr(Cell, "FAROE ISLANDS") > 0
Cell.Value = Replace(Cell, "FAROE ISLANDS", "FO")
Loop
Do While InStr(Cell, "FIJI") > 0
Cell.Value = Replace(Cell, "FIJI", "FJ")
Loop
Do While InStr(Cell, "FINLAND") > 0
Cell.Value = Replace(Cell, "FINLAND", "FI")
Loop
Do While InStr(Cell, "FRANCE") > 0
Cell.Value = Replace(Cell, "FRANCE", "FR")
Loop
Do While InStr(Cell, "FRENCH GUIANA") > 0
Cell.Value = Replace(Cell, "FRENCH GUIANA", "GF")
Loop
Do While InStr(Cell, "FRENCH SOUTHERN TERRITORIES") > 0
Cell.Value = Replace(Cell, "FRENCH SOUTHERN TERRITORIES", "TF")
Loop
Do While InStr(Cell, "GABON") > 0
Cell.Value = Replace(Cell, "GABON", "GA")
Loop
Do While InStr(Cell, "GAMBIA") > 0
Cell.Value = Replace(Cell, "GAMBIA", "GM")
Loop
Do While InStr(Cell, "GEORGIA") > 0
Cell.Value = Replace(Cell, "GEORGIA", "GE")
Loop
Do While InStr(Cell, "GERMANY") > 0
Cell.Value = Replace(Cell, "GERMANY", "DE")
Loop
Do While InStr(Cell, "GHANA") > 0
Cell.Value = Replace(Cell, "GHANA", "GH")
Loop
Do While InStr(Cell, "GIBRALTAR") > 0
Cell.Value = Replace(Cell, "GIBRALTAR", "GI")
Loop
Do While InStr(Cell, "GREAT BRITAIN") > 0
Cell.Value = Replace(Cell, "GREAT BRITAIN", "GB")
Loop
Do While InStr(Cell, "GREECE") > 0
Cell.Value = Replace(Cell, "GREECE", "GR")
Loop
Do While InStr(Cell, "GREENLAND") > 0
Cell.Value = Replace(Cell, "GREENLAND", "GL")
Loop
Do While InStr(Cell, "GRENADA") > 0
Cell.Value = Replace(Cell, "GRENADA", "GD")
Loop
Do While InStr(Cell, "GUADELOUPE (FRENCH)") > 0
Cell.Value = Replace(Cell, "GUADELOUPE (FRENCH)", "GP")
Loop
Do While InStr(Cell, "GUADELOUPE") > 0
Cell.Value = Replace(Cell, "GUADELOUPE", "GP")
Loop
Do While InStr(Cell, "GUAM (USA)") > 0
Cell.Value = Replace(Cell, "GUAM (USA)", "GU")
Loop
Do While InStr(Cell, "GUAM") > 0
Cell.Value = Replace(Cell, "GUAM", "GU")
Loop
Do While InStr(Cell, "GUATEMALA") > 0
Cell.Value = Replace(Cell, "GUATEMALA", "GT")
Loop
Do While InStr(Cell, "GUERNSEY") > 0
Cell.Value = Replace(Cell, "GUERNSEY", "GG")
Loop
Do While InStr(Cell, "GUINEA") > 0
Cell.Value = Replace(Cell, "GUINEA", "GN")
Loop
Do While InStr(Cell, "GUINEA BISSAU") > 0
Cell.Value = Replace(Cell, "GUINEA BISSAU", "GW")
Loop
Do While InStr(Cell, "GUYANA") > 0
Cell.Value = Replace(Cell, "GUYANA", "GY")
Loop
Do While InStr(Cell, "HAITI") > 0
Cell.Value = Replace(Cell, "HAITI", "HT")
Loop
Do While InStr(Cell, "HEARD ISLAND AND MCDONALD ISLANDS") > 0
Cell.Value = Replace(Cell, "HEARD ISLAND AND MCDONALD ISLANDS", "HM")
Loop
Do While InStr(Cell, "HONDURAS") > 0
Cell.Value = Replace(Cell, "HONDURAS", "HN")
Loop
Do While InStr(Cell, "HONG KONG") > 0
Cell.Value = Replace(Cell, "HONG KONG", "HK")
Loop
Do While InStr(Cell, "HUNGARY") > 0
Cell.Value = Replace(Cell, "HUNGARY", "HU")
Loop
Do While InStr(Cell, "ICELAND") > 0
Cell.Value = Replace(Cell, "ICELAND", "IS")
Loop
Do While InStr(Cell, "INDIA") > 0
Cell.Value = Replace(Cell, "INDIA", "IN")
Loop
Do While InStr(Cell, "INDONESIA") > 0
Cell.Value = Replace(Cell, "INDONESIA", "ID")
Loop
Do While InStr(Cell, "IRAN") > 0
Cell.Value = Replace(Cell, "IRAN", "IR")
Loop
Do While InStr(Cell, "IRAQ") > 0
Cell.Value = Replace(Cell, "IRAQ", "IQ")
Loop
Do While InStr(Cell, "IRELAND") > 0
Cell.Value = Replace(Cell, "IRELAND", "IE")
Loop
Do While InStr(Cell, "ISLE OF MAN") > 0
Cell.Value = Replace(Cell, "ISLE OF MAN", "IM")
Loop
Do While InStr(Cell, "ISRAEL") > 0
Cell.Value = Replace(Cell, "ISRAEL", "IL")
Loop
Do While InStr(Cell, "ITALY") > 0
Cell.Value = Replace(Cell, "ITALY", "IT")
Loop
Do While InStr(Cell, "IVORY COAST") > 0
Cell.Value = Replace(Cell, "IVORY COAST", "CI")
Loop
Do While InStr(Cell, "JAMAICA") > 0
Cell.Value = Replace(Cell, "JAMAICA", "JM")
Loop
Do While InStr(Cell, "JAPAN") > 0
Cell.Value = Replace(Cell, "JAPAN", "JP")
Loop
Do While InStr(Cell, "JERSEY") > 0
Cell.Value = Replace(Cell, "JERSEY", "JE")
Loop
Do While InStr(Cell, "JERSEY, CHANNEL ISLANDS") > 0
Cell.Value = Replace(Cell, "JERSEY, CHANNEL ISLANDS", "JE")
Loop
Do While InStr(Cell, "CHANNEL ISLANDS JERSEY") > 0
Cell.Value = Replace(Cell, "CHANNEL ISLANDS JERSEY", "JE")
Loop
Do While InStr(Cell, "JORDAN") > 0
Cell.Value = Replace(Cell, "JORDAN", "JO")
Loop
Do While InStr(Cell, "KAZAKHSTAN") > 0
Cell.Value = Replace(Cell, "KAZAKHSTAN", "KZ")
Loop
Do While InStr(Cell, "KENYA") > 0
Cell.Value = Replace(Cell, "KENYA", "KE")
Loop
Do While InStr(Cell, "KIRIBATI") > 0
Cell.Value = Replace(Cell, "KIRIBATI", "KI")
Loop
Do While InStr(Cell, "KOREA-NORTH") > 0
Cell.Value = Replace(Cell, "KOREA-NORTH", "KP")
Loop
Do While InStr(Cell, "KOREA NORTH") > 0
Cell.Value = Replace(Cell, "KOREA NORTH", "KP")
Loop
Do While InStr(Cell, "NORTH KOREA") > 0
Cell.Value = Replace(Cell, "NORTH KOREA", "KP")
Loop
Do While InStr(Cell, "KOREA") > 0
Cell.Value = Replace(Cell, "KOREA", "KR")
Loop
Do While InStr(Cell, "KOREA-SOUTH") > 0
Cell.Value = Replace(Cell, "KOREA-SOUTH", "KR")
Loop
Do While InStr(Cell, "KOREA SOUTH") > 0
Cell.Value = Replace(Cell, "KOREA SOUTH", "KR")
Loop
Do While InStr(Cell, "SOUTH KOREA") > 0
Cell.Value = Replace(Cell, "SOUTH KOREA", "KR")
Loop
Do While InStr(Cell, "KUWAIT") > 0
Cell.Value = Replace(Cell, "KUWAIT", "KW")
Loop
Do While InStr(Cell, "KYRGYZSTAN") > 0
Cell.Value = Replace(Cell, "KYRGYZSTAN", "KG")
Loop
Do While InStr(Cell, "LAOS") > 0
Cell.Value = Replace(Cell, "LAOS", "LA")
Loop
Do While InStr(Cell, "LATVIA") > 0
Cell.Value = Replace(Cell, "LATVIA", "LV")
Loop
Do While InStr(Cell, "LEBANON") > 0
Cell.Value = Replace(Cell, "LEBANON", "LB")
Loop
Do While InStr(Cell, "LESOTHO") > 0
Cell.Value = Replace(Cell, "LESOTHO", "LS")
Loop
Do While InStr(Cell, "LIBERIA") > 0
Cell.Value = Replace(Cell, "LIBERIA", "LR")
Loop
Do While InStr(Cell, "LIBYA") > 0
Cell.Value = Replace(Cell, "LIBYA", "LY")
Loop
Do While InStr(Cell, "LIECHTENSTEIN") > 0
Cell.Value = Replace(Cell, "LIECHTENSTEIN", "LI")
Loop
Do While InStr(Cell, "LITHUANIA") > 0
Cell.Value = Replace(Cell, "LITHUANIA", "LT")
Loop
Do While InStr(Cell, "LUXEMBOURG") > 0
Cell.Value = Replace(Cell, "LUXEMBOURG", "LU")
Loop
Do While InStr(Cell, "MACAU") > 0
Cell.Value = Replace(Cell, "MACAU", "MO")
Loop
Do While InStr(Cell, "MACEDONIA") > 0
Cell.Value = Replace(Cell, "MACEDONIA", "MK")
Loop
Do While InStr(Cell, "MADAGASCAR") > 0
Cell.Value = Replace(Cell, "MADAGASCAR", "MG")
Loop
Do While InStr(Cell, "MALAWI") > 0
Cell.Value = Replace(Cell, "MALAWI", "MW")
Loop
Do While InStr(Cell, "MALAYSIA") > 0
Cell.Value = Replace(Cell, "MALAYSIA", "MY")
Loop
Do While InStr(Cell, "MALDIVES") > 0
Cell.Value = Replace(Cell, "MALDIVES", "MV")
Loop
Do While InStr(Cell, "MALI") > 0
Cell.Value = Replace(Cell, "MALI", "ML")
Loop
Do While InStr(Cell, "MALTA") > 0
Cell.Value = Replace(Cell, "MALTA", "MT")
Loop
Do While InStr(Cell, "MARSHALL ISLANDS") > 0
Cell.Value = Replace(Cell, "MARSHALL ISLANDS", "MH")
Loop
Do While InStr(Cell, "MARTINIQUE (FRENCH)") > 0
Cell.Value = Replace(Cell, "MARTINIQUE (FRENCH)", "MQ")
Loop
Do While InStr(Cell, "MARTINIQUE") > 0
Cell.Value = Replace(Cell, "MARTINIQUE", "MQ")
Loop
Do While InStr(Cell, "MAURITANIA") > 0
Cell.Value = Replace(Cell, "MAURITANIA", "MR")
Loop
Do While InStr(Cell, "MAURITIUS") > 0
Cell.Value = Replace(Cell, "MAURITIUS", "MU")
Loop
Do While InStr(Cell, "MAYOTTE") > 0
Cell.Value = Replace(Cell, "MAYOTTE", "YT")
Loop
Do While InStr(Cell, "MEXICO") > 0
Cell.Value = Replace(Cell, "MEXICO", "MX")
Loop
Do While InStr(Cell, "MICRONESIA") > 0
Cell.Value = Replace(Cell, "MICRONESIA", "FM")
Loop
Do While InStr(Cell, "MOLDOVA") > 0
Cell.Value = Replace(Cell, "MOLDOVA", "MD")
Loop
Do While InStr(Cell, "MONACO") > 0
Cell.Value = Replace(Cell, "MONACO", "MC")
Loop
Do While InStr(Cell, "MONGOLIA") > 0
Cell.Value = Replace(Cell, "MONGOLIA", "MN")
Loop
Do While InStr(Cell, "MONTENEGRO") > 0
Cell.Value = Replace(Cell, "MONTENEGRO", "ME")
Loop
Do While InStr(Cell, "MONTSERRAT") > 0
Cell.Value = Replace(Cell, "MONTSERRAT", "MS")
Loop
Do While InStr(Cell, "MOROCCO") > 0
Cell.Value = Replace(Cell, "MOROCCO", "MA")
Loop
Do While InStr(Cell, "MOZAMBIQUE") > 0
Cell.Value = Replace(Cell, "MOZAMBIQUE", "MZ")
Loop
Do While InStr(Cell, "MYANMAR") > 0
Cell.Value = Replace(Cell, "MYANMAR", "MM")
Loop
Do While InStr(Cell, "NAMIBIA") > 0
Cell.Value = Replace(Cell, "NAMIBIA", "NA")
Loop
Do While InStr(Cell, "NAURU") > 0
Cell.Value = Replace(Cell, "NAURU", "NR")
Loop
Do While InStr(Cell, "NEPAL") > 0
Cell.Value = Replace(Cell, "NEPAL", "NP")
Loop
Do While InStr(Cell, "NETHERLANDS") > 0
Cell.Value = Replace(Cell, "NETHERLANDS", "NL")
Loop
Do While InStr(Cell, "THE NETHERLANDS") > 0
Cell.Value = Replace(Cell, "THE NETHERLANDS", "NL")
Loop
Do While InStr(Cell, "NETHERLANDS ANTILLES") > 0
Cell.Value = Replace(Cell, "NETHERLANDS ANTILLES", "AN")
Loop
Do While InStr(Cell, "CURACAO") > 0
Cell.Value = Replace(Cell, "CURACAO", "AN")
Loop
Do While InStr(Cell, "SAINT MAARTEN") > 0
Cell.Value = Replace(Cell, "SAINT MAARTEN", "AN")
Loop
Do While InStr(Cell, "ST MAARTEN") > 0
Cell.Value = Replace(Cell, "ST MAARTEN", "AN")
Loop
Do While InStr(Cell, "ST. MAARTEN") > 0
Cell.Value = Replace(Cell, "ST. MAARTEN", "AN")
Loop
Do While InStr(Cell, "SAINT EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "SAINT EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "ST EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "ST EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "ST. EUSTATIUS AND SABA") > 0
Cell.Value = Replace(Cell, "ST. EUSTATIUS AND SABA", "AN")
Loop
Do While InStr(Cell, "NEW CALEDONIA (FRENCH)") > 0
Cell.Value = Replace(Cell, "NEW CALEDONIA (FRENCH)", "NC")
Loop
Do While InStr(Cell, "NEW CALEDONIA") > 0
Cell.Value = Replace(Cell, "NEW CALEDONIA", "NC")
Loop
Do While InStr(Cell, "NEW ZEALAND") > 0
Cell.Value = Replace(Cell, "NEW ZEALAND", "NZ")
Loop
Do While InStr(Cell, "NICARAGUA") > 0
Cell.Value = Replace(Cell, "NICARAGUA", "NI")
Loop
Do While InStr(Cell, "NIGER") > 0
Cell.Value = Replace(Cell, "NIGER", "NE")
Loop
Do While InStr(Cell, "NIGERIA") > 0
Cell.Value = Replace(Cell, "NIGERIA", "NG")
Loop
Do While InStr(Cell, "NIUE") > 0
Cell.Value = Replace(Cell, "NIUE", "NU")
Loop
Do While InStr(Cell, "NORFOLK ISLAND") > 0
Cell.Value = Replace(Cell, "NORFOLK ISLAND", "NF")
Loop
Do While InStr(Cell, "NORTHERN MARIANA ISLANDS") > 0
Cell.Value = Replace(Cell, "NORTHERN MARIANA ISLANDS", "MP")
Loop
Do While InStr(Cell, "NORWAY") > 0
Cell.Value = Replace(Cell, "NORWAY", "NO")
Loop
Do While InStr(Cell, "OMAN") > 0
Cell.Value = Replace(Cell, "OMAN", "OM")
Loop
Do While InStr(Cell, "PAKISTAN") > 0
Cell.Value = Replace(Cell, "PAKISTAN", "PK")
Loop
Do While InStr(Cell, "PALAU") > 0
Cell.Value = Replace(Cell, "PALAU", "PW")
Loop
Do While InStr(Cell, "PALESTINE") > 0
Cell.Value = Replace(Cell, "PALESTINE", "PS")
Loop
Do While InStr(Cell, "PALESTINE, STATE OF") > 0
Cell.Value = Replace(Cell, "PALESTINE, STATE OF", "PS")
Loop
Do While InStr(Cell, "STATE OF PALESTINE") > 0
Cell.Value = Replace(Cell, "STATE OF PALESTINE", "PS")
Loop
Do While InStr(Cell, "PANAMA") > 0
Cell.Value = Replace(Cell, "PANAMA", "PA")
Loop
Do While InStr(Cell, "PAPUA NEW GUINEA") > 0
Cell.Value = Replace(Cell, "PAPUA NEW GUINEA", "PG")
Loop
Do While InStr(Cell, "PARAGUAY") > 0
Cell.Value = Replace(Cell, "PARAGUAY", "PY")
Loop
Do While InStr(Cell, "PERU") > 0
Cell.Value = Replace(Cell, "PERU", "PE")
Loop
Do While InStr(Cell, "PHILIPPINES") > 0
Cell.Value = Replace(Cell, "PHILIPPINES", "PH")
Loop
Do While InStr(Cell, "PITCAIRN ISLAND") > 0
Cell.Value = Replace(Cell, "PITCAIRN ISLAND", "PN")
Loop
Do While InStr(Cell, "POLAND") > 0
Cell.Value = Replace(Cell, "POLAND", "PL")
Loop
Do While InStr(Cell, "POLYNESIA (FRENCH)") > 0
Cell.Value = Replace(Cell, "POLYNESIA (FRENCH)", "PF")
Loop
Do While InStr(Cell, "POLYNESIA") > 0
Cell.Value = Replace(Cell, "POLYNESIA", "PF")
Loop
Do While InStr(Cell, "FRENCH POLYNESIA") > 0
Cell.Value = Replace(Cell, "FRENCH POLYNESIA", "PF")
Loop
Do While InStr(Cell, "PORTUGAL") > 0
Cell.Value = Replace(Cell, "PORTUGAL", "PT")
Loop
Do While InStr(Cell, "PUERTO RICO") > 0
Cell.Value = Replace(Cell, "PUERTO RICO", "PR")
Loop
Do While InStr(Cell, "QATAR") > 0
Cell.Value = Replace(Cell, "QATAR", "QA")
Loop
Do While InStr(Cell, "REUNION (FRENCH)") > 0
Cell.Value = Replace(Cell, "REUNION (FRENCH)", "RE")
Loop
Do While InStr(Cell, "REUNION") > 0
Cell.Value = Replace(Cell, "REUNION", "RE")
Loop
Do While InStr(Cell, "FRENCH REUNION") > 0
Cell.Value = Replace(Cell, "FRENCH REUNION", "RE")
Loop
Do While InStr(Cell, "ROMANIA") > 0
Cell.Value = Replace(Cell, "ROMANIA", "RO")
Loop
Do While InStr(Cell, "RUSSIA") > 0
Cell.Value = Replace(Cell, "RUSSIA", "RU")
Loop
Do While InStr(Cell, "RUSSIAN FEDERATION") > 0
Cell.Value = Replace(Cell, "RUSSIAN FEDERATION", "RU")
Loop
Do While InStr(Cell, "RWANDA") > 0
Cell.Value = Replace(Cell, "RWANDA", "RW")
Loop
Do While InStr(Cell, "SAINT HELENA") > 0
Cell.Value = Replace(Cell, "SAINT HELENA", "SH")
Loop
Do While InStr(Cell, "SAINT KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "SAINT KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "ST KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "ST KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "ST. KITTS & NEVIS ANGUILLA") > 0
Cell.Value = Replace(Cell, "ST. KITTS & NEVIS ANGUILLA", "KN")
Loop
Do While InStr(Cell, "SAINT LUCIA") > 0
Cell.Value = Replace(Cell, "SAINT LUCIA", "LC")
Loop
Do While InStr(Cell, "ST LUCIA") > 0
Cell.Value = Replace(Cell, "ST LUCIA", "LC")
Loop
Do While InStr(Cell, "ST. LUCIA") > 0
Cell.Value = Replace(Cell, "ST. LUCIA", "LC")
Loop
Do While InStr(Cell, "SAINT PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "SAINT PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "ST PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "ST PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "ST. PIERRE AND MIQUELON") > 0
Cell.Value = Replace(Cell, "ST. PIERRE AND MIQUELON", "PM")
Loop
Do While InStr(Cell, "SAINT VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "SAINT VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "ST VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "ST VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "ST. VINCENT & GRENADINES") > 0
Cell.Value = Replace(Cell, "ST. VINCENT & GRENADINES", "VC")
Loop
Do While InStr(Cell, "SAMOA") > 0
Cell.Value = Replace(Cell, "SAMOA", "WS")
Loop
Do While InStr(Cell, "SAN MARINO") > 0
Cell.Value = Replace(Cell, "SAN MARINO", "SM")
Loop
Do While InStr(Cell, "SAO TOME AND PRINCIPE") > 0
Cell.Value = Replace(Cell, "SAO TOME AND PRINCIPE", "ST")
Loop
Do While InStr(Cell, "SAUDI ARABIA") > 0
Cell.Value = Replace(Cell, "SAUDI ARABIA", "SA")
Loop
Do While InStr(Cell, "SENEGAL") > 0
Cell.Value = Replace(Cell, "SENEGAL", "SN")
Loop
Do While InStr(Cell, "SERBIA") > 0
Cell.Value = Replace(Cell, "SERBIA", "RS")
Loop
Do While InStr(Cell, "SEYCHELLES") > 0
Cell.Value = Replace(Cell, "SEYCHELLES", "SC")
Loop
Do While InStr(Cell, "SIERRA LEONE") > 0
Cell.Value = Replace(Cell, "SIERRA LEONE", "SL")
Loop
Do While InStr(Cell, "SINGAPORE") > 0
Cell.Value = Replace(Cell, "SINGAPORE", "SG")
Loop
Do While InStr(Cell, "SLOVAKIA") > 0
Cell.Value = Replace(Cell, "SLOVAKIA", "SK")
Loop
Do While InStr(Cell, "SLOVENIA") > 0
Cell.Value = Replace(Cell, "SLOVENIA", "SI")
Loop
Do While InStr(Cell, "SOLOMON ISLANDS") > 0
Cell.Value = Replace(Cell, "SOLOMON ISLANDS", "SB")
Loop
Do While InStr(Cell, "SOMALIA") > 0
Cell.Value = Replace(Cell, "SOMALIA", "SO")
Loop
Do While InStr(Cell, "SOUTH AFRICA") > 0
Cell.Value = Replace(Cell, "SOUTH AFRICA", "ZA")
Loop
Do While InStr(Cell, "SOUTH GEORGIA & SOUTH SANDWICH ISLANDS") > 0
Cell.Value = Replace(Cell, "SOUTH GEORGIA & SOUTH SANDWICH ISLANDS", "GS")
Loop
Do While InStr(Cell, "SOUTH SUDAN") > 0
Cell.Value = Replace(Cell, "SOUTH SUDAN", "SS")
Loop
Do While InStr(Cell, "SPAIN") > 0
Cell.Value = Replace(Cell, "SPAIN", "ES")
Loop
Do While InStr(Cell, "SRI LANKA") > 0
Cell.Value = Replace(Cell, "SRI LANKA", "LK")
Loop
Do While InStr(Cell, "SUDAN") > 0
Cell.Value = Replace(Cell, "SUDAN", "SD")
Loop
Do While InStr(Cell, "SURINAME") > 0
Cell.Value = Replace(Cell, "SURINAME", "SR")
Loop
Do While InStr(Cell, "SVALBARD AND JAN MAYEN ISLANDS") > 0
Cell.Value = Replace(Cell, "SVALBARD AND JAN MAYEN ISLANDS", "SJ")
Loop
Do While InStr(Cell, "SWAZILAND") > 0
Cell.Value = Replace(Cell, "SWAZILAND", "SZ")
Loop
Do While InStr(Cell, "SWEDEN") > 0
Cell.Value = Replace(Cell, "SWEDEN", "SE")
Loop
Do While InStr(Cell, "SWITZERLAND") > 0
Cell.Value = Replace(Cell, "SWITZERLAND", "CH")
Loop
Do While InStr(Cell, "SYRIA") > 0
Cell.Value = Replace(Cell, "SYRIA", "SY")
Loop
Do While InStr(Cell, "TAIWAN") > 0
Cell.Value = Replace(Cell, "TAIWAN", "TW")
Loop
Do While InStr(Cell, "TAJIKISTAN") > 0
Cell.Value = Replace(Cell, "TAJIKISTAN", "TJ")
Loop
Do While InStr(Cell, "TANZANIA") > 0
Cell.Value = Replace(Cell, "TANZANIA", "TZ")
Loop
Do While InStr(Cell, "TANZANIA, UNITED REPUBLIC") > 0
Cell.Value = Replace(Cell, "TANZANIA, UNITED REPUBLIC", "TZ")
Loop
Do While InStr(Cell, "TANZANIA, UNITED REPUBLIC OF") > 0
Cell.Value = Replace(Cell, "TANZANIA, UNITED REPUBLIC OF", "TZ")
Loop
Do While InStr(Cell, "UNITED REPUBLIC OF TANZANIA") > 0
Cell.Value = Replace(Cell, "UNITED REPUBLIC OF TANZANIA", "TZ")
Loop
Do While InStr(Cell, "THAILAND") > 0
Cell.Value = Replace(Cell, "THAILAND", "TH")
Loop
Do While InStr(Cell, "TOGO") > 0
Cell.Value = Replace(Cell, "TOGO", "TG")
Loop
Do While InStr(Cell, "TOKELAU") > 0
Cell.Value = Replace(Cell, "TOKELAU", "TK")
Loop
Do While InStr(Cell, "TONGA") > 0
Cell.Value = Replace(Cell, "TONGA", "TO")
Loop
Do While InStr(Cell, "TRINIDAD AND TOBAGO") > 0
Cell.Value = Replace(Cell, "TRINIDAD AND TOBAGO", "TT")
Loop
Do While InStr(Cell, "TUNISIA") > 0
Cell.Value = Replace(Cell, "TUNISIA", "TN")
Loop
Do While InStr(Cell, "TURKEY") > 0
Cell.Value = Replace(Cell, "TURKEY", "TR")
Loop
Do While InStr(Cell, "TURKMENISTAN") > 0
Cell.Value = Replace(Cell, "TURKMENISTAN", "TM")
Loop
Do While InStr(Cell, "TURKS AND CAICOS ISLANDS") > 0
Cell.Value = Replace(Cell, "TURKS AND CAICOS ISLANDS", "TC")
Loop
Do While InStr(Cell, "TUVALU") > 0
Cell.Value = Replace(Cell, "TUVALU", "TV")
Loop
Do While InStr(Cell, "U.K.") > 0
Cell.Value = Replace(Cell, "U.K.", "GB")
Loop
Do While InStr(Cell, "UK") > 0
Cell.Value = Replace(Cell, "UK", "GB")
Loop
Do While InStr(Cell, "UNITED KINGDOM") > 0
Cell.Value = Replace(Cell, "UNITED KINGDOM", "GB")
Loop
Do While InStr(Cell, "UGANDA") > 0
Cell.Value = Replace(Cell, "UGANDA", "UG")
Loop
Do While InStr(Cell, "UKRAINE") > 0
Cell.Value = Replace(Cell, "UKRAINE", "UA")
Loop
Do While InStr(Cell, "UNITED ARAB EMIRATES") > 0
Cell.Value = Replace(Cell, "UNITED ARAB EMIRATES", "AE")
Loop
Do While InStr(Cell, "URUGUAY") > 0
Cell.Value = Replace(Cell, "URUGUAY", "UY")
Loop
Do While InStr(Cell, "USA") > 0
Cell.Value = Replace(Cell, "USA", "US")
Loop
Do While InStr(Cell, "UNITED STATES") > 0
Cell.Value = Replace(Cell, "UNITED STATES", "US")
Loop
Do While InStr(Cell, "UNITED STATES MINOR OUTLYING ISLANDS") > 0
Cell.Value = Replace(Cell, "UNITED STATES MINOR OUTLYING ISLANDS", "UM")
Loop
Do While InStr(Cell, "USA MINOR OUTLYING ISLANDS") > 0
Cell.Value = Replace(Cell, "USA MINOR OUTLYING ISLANDS", "UM")
Loop
Do While InStr(Cell, "UZBEKISTAN") > 0
Cell.Value = Replace(Cell, "UZBEKISTAN", "UZ")
Loop
Do While InStr(Cell, "VANUATU") > 0
Cell.Value = Replace(Cell, "VANUATU", "VU")
Loop
Do While InStr(Cell, "VATICAN") > 0
Cell.Value = Replace(Cell, "VATICAN", "VA")
Loop
Do While InStr(Cell, "VENEZUELA") > 0
Cell.Value = Replace(Cell, "VENEZUELA", "VE")
Loop
Do While InStr(Cell, "VIETNAM") > 0
Cell.Value = Replace(Cell, "VIETNAM", "VN")
Loop
Do While InStr(Cell, "VIRGIN ISLANDS (BRITISH)") > 0
Cell.Value = Replace(Cell, "VIRGIN ISLANDS (BRITISH)", "VG")
Loop
Do While InStr(Cell, "BRITISH VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "BRITISH VIRGIN ISLANDS", "VG")
Loop
Do While InStr(Cell, "VIRGIN ISLANDS (USA)") > 0
Cell.Value = Replace(Cell, "VIRGIN ISLANDS (USA)", "VI")
Loop
Do While InStr(Cell, "USA VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "USA VIRGIN ISLANDS", "VI")
Loop
Do While InStr(Cell, "UNITED STATES VIRGIN ISLANDS") > 0
Cell.Value = Replace(Cell, "UNITED STATES VIRGIN ISLANDS", "VI")
Loop
Do While InStr(Cell, "WALLIS AND FUTUNA ISLANDS") > 0
Cell.Value = Replace(Cell, "WALLIS AND FUTUNA ISLANDS", "WF")
Loop
Do While InStr(Cell, "WESTERN SAHARA") > 0
Cell.Value = Replace(Cell, "WESTERN SAHARA", "EH")
Loop
Do While InStr(Cell, "YEMEN") > 0
Cell.Value = Replace(Cell, "YEMEN", "YE")
Loop
Do While InStr(Cell, "ZAMBIA") > 0
Cell.Value = Replace(Cell, "ZAMBIA", "ZM")
Loop
Do While InStr(Cell, "ZIMBABWE") > 0
Cell.Value = Replace(Cell, "ZIMBABWE", "ZW")
Loop

Next

End Sub

Reorganize

Code:

Sub ColumnReorganise()'
' Bulk Macro
'

Dim iRow As Long
Dim iCol As Long


'Constant values
data_sheet1 = "Bulk (2)" 'Specify the sheet that needs to be reorganised
target_sheet = "Final Report" 'Specify the sheet to store the results
iRow = Sheets(data_sheet1).UsedRange.Rows.Count 'Determine how many rows are in use


'Create a new sheet to store the results
Worksheets.Add.Name = "Final Report"


'Start organizing columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count


    'Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
    TargetCol = 0


    'Read the header of the original sheet to determine the column order
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Legacy System Client ID (16)" Then TargetCol = 1
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Process Center (2)" Then TargetCol = 2
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Short Name (30)" Then TargetCol = 3
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bene Name (35)" Then TargetCol = 4
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Currency Code" Then TargetCol = 5
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Vendor Account (30)" Then TargetCol = 6
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank Account (34)" Then TargetCol = 7
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Address Line 1 (35)" Then TargetCol = 8
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Address Line 2 (35)" Then TargetCol = 9
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Address Line 3 (35)" Then TargetCol = 10
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Country Code (2)" Then TargetCol = 11
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Ref. Line 1" Then TargetCol = 12
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Ref. Line 2" Then TargetCol = 13
    If Sheets(data_sheet1).Cells(1, iCol).Value = "SWIFT Branch Details" Then TargetCol = 14
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank Name (35)" Then TargetCol = 15
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank SWIFT Address (11)" Then TargetCol = 16
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank Routing Code (29)" Then TargetCol = 17
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank Address Line 1 (35)" Then TargetCol = 18
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank Address Line 2 (35)" Then TargetCol = 19
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank Address Line 3 (35)" Then TargetCol = 20
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Bank Country Code (2)" Then TargetCol = 21
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK Code" Then TargetCol = 22
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Intermediary Bank Name (35)" Then TargetCol = 23
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK SWIFT Code (11)" Then TargetCol = 24
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK Routing Code (29)" Then TargetCol = 25
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK Account (34)" Then TargetCol = 26
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK Address Line 1" Then TargetCol = 27
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK Address Line 2" Then TargetCol = 28
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK Address Line 3" Then TargetCol = 29
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBK Country Code" Then TargetCol = 30
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Default Instrument Code (32)" Then TargetCol = 31
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Draft Enabled (4)" Then TargetCol = 32
    If Sheets(data_sheet1).Cells(1, iCol).Value = "EFT Enabled (4)" Then TargetCol = 33
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Use IBK Info Y/N (4)" Then TargetCol = 34
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Notify (4)" Then TargetCol = 35
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Email Address 1 (255)" Then TargetCol = 36
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Email Address 2" Then TargetCol = 37
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Notify Language Code (10)" Then TargetCol = 38
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Low Value Routing Code" Then TargetCol = 39
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Low Value Enabled Y/N" Then TargetCol = 40
    If Sheets(data_sheet1).Cells(1, iCol).Value = "Line 72 Field Type" Then TargetCol = 41
    If Sheets(data_sheet1).Cells(1, iCol).Value = "SenderInfoLine1 O" Then TargetCol = 42
    If Sheets(data_sheet1).Cells(1, iCol).Value = "SenderInfoLine2 O" Then TargetCol = 43
    If Sheets(data_sheet1).Cells(1, iCol).Value = "SenderInfoLine3 O" Then TargetCol = 44
    If Sheets(data_sheet1).Cells(1, iCol).Value = "SenderInfoLine4 O" Then TargetCol = 45
    If Sheets(data_sheet1).Cells(1, iCol).Value = "SenderInfoLine5 O" Then TargetCol = 46
    If Sheets(data_sheet1).Cells(1, iCol).Value = "SenderInfoLine6 O" Then TargetCol = 47
    If Sheets(data_sheet1).Cells(1, iCol).Value = "LegacySystemBeneficiaryID (20) CM" Then TargetCol = 48
    If Sheets(data_sheet1).Cells(1, iCol).Value = "ChargeType O" Then TargetCol = 49
    If Sheets(data_sheet1).Cells(1, iCol).Value = "BeneficiaryType (100) O" Then TargetCol = 50
    If Sheets(data_sheet1).Cells(1, iCol).Value = "BenePhoneNumber(20)" Then TargetCol = 51
    If Sheets(data_sheet1).Cells(1, iCol).Value = "DraftEncashmentCountry_ID" Then TargetCol = 52
    If Sheets(data_sheet1).Cells(1, iCol).Value = "PostalCode(35)" Then TargetCol = 53
    If Sheets(data_sheet1).Cells(1, iCol).Value = "BankPostalCode(35)" Then TargetCol = 54
    If Sheets(data_sheet1).Cells(1, iCol).Value = "IBankPostalCode(35)" Then TargetCol = 55
    If Sheets(data_sheet1).Cells(1, iCol).Value = "AlternateShipToName(140)" Then TargetCol = 56
    If Sheets(data_sheet1).Cells(1, iCol).Value = "ContactName(140)" Then TargetCol = 57
    If Sheets(data_sheet1).Cells(1, iCol).Value = "DefaultCarrier" Then TargetCol = 58
    If Sheets(data_sheet1).Cells(1, iCol).Value = "DrafttoBeneEnabled" Then TargetCol = 59
    If Sheets(data_sheet1).Cells(1, iCol).Value = "WUCashEnabled(4)" Then TargetCol = 60
    If Sheets(data_sheet1).Cells(1, iCol).Value = "WUMobileEnabled(4)" Then TargetCol = 61
    If Sheets(data_sheet1).Cells(1, iCol).Value = "WUMobilePhoneNumber(20)" Then TargetCol = 62
    If Sheets(data_sheet1).Cells(1, iCol).Value = "WUReceiverFirstName(30)" Then TargetCol = 63
    If Sheets(data_sheet1).Cells(1, iCol).Value = "WUReceiverLastName(35)" Then TargetCol = 64


    'If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
    If TargetCol <> 0 Then
        'Select the column and copy it
        Sheets(data_sheet1).Range(Sheets(data_sheet1).Cells(1, iCol), Sheets(data_sheet1).Cells(iRow, iCol)).Copy Destination:=Sheets(target_sheet).Cells(1, TargetCol)
    End If


Next iCol 'Move to the next column until all columns are read


'


ActiveSheet.Cells.Borders.LineStyle = xlLineStyleNone


ActiveSheet.Cells.Interior.Color = xlColorIndexNone


End Sub

yky · Jun 1, 2016

You have so many Ifs without End If. Why doesn't compiler complain?

This may not correct the problem but it will speed up the program. Copy all the If's starting from 2 to 64, paste it to notepad, and replace all the "If " with "ElseIf", copy and paste it back to where it is, then add "End If" after "= 64". This way, as soon as one If condition is met, it will skip the rest Ifs and jump to "End If".

Code:

If Sheets(data_sheet1).Cells(1, iCol).Value = "Process Center (2)" Then TargetCol = 2
.......
.......
If Sheets(data_sheet1).Cells(1, iCol).Value = "WUReceiverLastName(35)" Then TargetCol = 64

pupsia · Jun 2, 2016

yky said:
You have so many Ifs without End If. Why doesn't compiler complain?

This may not correct the problem but it will speed up the program. Copy all the If's starting from 2 to 64, paste it to notepad, and replace all the "If " with "ElseIf", copy and paste it back to where it is, then add "End If" after "= 64". This way, as soon as one If condition is met, it will skip the rest Ifs and jump to "End If".

The thing is, when I run the Reorganize marco, I need all Ifs to run, not to skip the rest.
Tried Else If at some point, but for some reason the macro didn't run...

Any ideas on why my 2 marco do not work together? The Country Code and the Reorganize.

wideboydixon · Jun 2, 2016

Yes I may do. Your column headers are getting converted to upper case and then proper case by your ConvertToUpperCase() macro but you're looking for something else. Take column 1 for example. Your macro looks for "Legacy System Client ID (16)" but the CovertToUpperCase() will have converted it first to "LEGACY SYSTEM CLIENT ID (16)" and then "Legacy System Client Id (16)" - note the lower case "d" on "Id". You either need to stop your ConvertToUpperCase() macro from corrupting the column headers or you need to use StrComp to make the comparison:

Code:

If StrComp(Sheets(data_sheet1).Cells(1, iCol).Value, "Legacy System Client ID (16)", vbTextCompare) = 0 Then TargetCol = 1

etc.

WBD

pupsia · Jun 2, 2016

wideboydixon said:
Yes I may do. Your column headers are getting converted to upper case and then proper case by your ConvertToUpperCase() macro but you're looking for something else. Take column 1 for example. Your macro looks for "Legacy System Client ID (16)" but the CovertToUpperCase() will have converted it first to "LEGACY SYSTEM CLIENT ID (16)" and then "Legacy System Client Id (16)" - note the lower case "d" on "Id". You either need to stop your ConvertToUpperCase() macro from corrupting the column headers or you need to use StrComp to make the comparison:

Code:

If StrComp(Sheets(data_sheet1).Cells(1, iCol).Value, "Legacy System Client ID (16)", vbTextCompare) = 0 Then TargetCol = 1

etc.

WBD

Oh my god! That totally makes seance! I`ll go try it out now, and test it.
Thank you so much!!

I was wondering, this "Proper Case" part is the problem, is there a way to make the whole document "Upper Case" excluding the first line?
That would solve everything, without me changing all the formulas to match the text.
I was trying to find something like this, but no luck in that...

pupsia · Jun 2, 2016

I played a bit, and decided to change the way I change to Upper Case all-together.
The new formula That I`m using is the one below.
So far the testing went well, and the data was changed to Upper Case.

Code:

Sub test()'
' test Macro
'
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
For Each x In Selection
x.Value = UCase(x.Value)
Next

    Range("A2").Select

'
End Sub

Problem with running 2 different macros

pupsia

Board Regular

Excel Facts

yky

Well-known Member

pupsia

Board Regular

wideboydixon

Well-known Member

pupsia

Board Regular

pupsia

Board Regular

Similar threads

Forum statistics

Share this page

Problem with running 2 different macros

pupsia

Board Regular

Excel Facts

yky

Well-known Member

pupsia

Board Regular

wideboydixon

Well-known Member

pupsia

Board Regular

pupsia

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock