Problem with running 2 different macros

pupsia

Board Regular
Joined
Dec 2, 2015
Messages
67
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,874
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 2, 2015
Messages
67
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.
 
Last edited:

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
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

Board Regular
Joined
Dec 2, 2015
Messages
67
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

Board Regular
Joined
Dec 2, 2015
Messages
67
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
 
Last edited:

Forum statistics

Threads
1,136,792
Messages
5,677,761
Members
419,718
Latest member
ALWP

We've detected that you are using an adblocker.

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

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

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

Disable uBlock

Follow these easy steps to disable uBlock

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