VBA: Why Doesn't this macro do anything?

SteveOranjinSteve · Mar 13, 2020

The following code is just supposed to make a bunch of replacements. Its fairly straightforward. I originally had a comma between, "Replace" and "LookAt" but I got a Missing Arguement" required. Through trial and error I was able to get the macro to run when I removed that comma. It ran, but it didn't do anything.

I've tried the following:

Changing the match case to false.
Remove the comma between replace and LookAt.

The following is what you should be aware of:

The Data itself is in a comma separated list. So its like this, "Angola, Belgium, Bolivia, Croatia, Greece, Indonesia" etc... etc...
There are about 134 rows of data or so.
Sometimes there can be No data in a cell, sometimes there may be 15 to 20 countries in a cell.

I can't think of any other parameters you should be aware of. Hope you're well,

Steve

VBA Code:

Sub Macro1()
'
' Macro1 Macro
'

'

    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Macedonia (Republic of)", Replacement:="Macedonia", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Uae", Replacement:="United Arab Emirates", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Uae", Replacement:="United Arab Emirates", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="SouthAfrica", Replacement:="South Africa", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Venezuela (Bolivarian Republic)", Replacement:="Venezuela", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="BosniaandHerzegovina", Replacement:="Bosnia and Herzegovina", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="CzechRepublic", Replacement:="Czech Republic", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="HongKong", Replacement:="Hong Kong", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Korea (South)", Replacement:="South Korea", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Korea(South)", Replacement:="South Korea", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="RussianFederation", Replacement:="Russian Federation", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Russian Federation", Replacement:="Russia", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="SARChina", Replacement:="SAR China", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="SAR China", Replacement:="China", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="SaudiArabia", Replacement:="Saudi Arabia", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="SouthAfrica", Replacement:="South Africa", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="CostaRica", Replacement:="Costa Rica", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="PuertoRico", Replacement:="Puerto Rico", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="PuertoRico", Replacement:="Puerto Rico", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="SaintPierreandMiquelon", Replacement:="Saint Pierre and Miquelon", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="UnitedKingdom", Replacement:="United Kingdom", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="UnitedStatesofAmerica", Replacement:="United States of America", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="SyrianArabRepublic(Syria)", Replacement:="Syrian Arab Republic (Syria)", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Syrian Arab Republic (Syria)", Replacement:="Syria", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Tanzania(UnitedRepublicof)", Replacement:="Tanzania (United Republic of)", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Tanzania (United Republic of)", Replacement:="Tanzania", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Taiwan(RepublicofChina)", Replacement:="Taiwan (Republic of China)", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="Taiwan (Republic of China)", Replacement:="Taiwan", _
        SearchOrder:=xlByColumns, MatchCase:=True
    Worksheets("Sheet1").Columns("H").Replace LookAt:=xlPart, _
        What:="TrinidadandTobago", Replacement:="Trinidad and Tobago", _
        SearchOrder:=xlByColumns, MatchCase:=True


End Sub

SteveOranjinSteve · Mar 13, 2020

Any given listing can be in a cell of its own, or apart of a larger string. I'm going to copy and paste two strings that are examples of how they look now for your review below.

Angola, Belgium, Bolivia, Brazil, Bulgaria, Croatia, Greece, Indonesia, Iraq, Israel, Kosovo, Macedonia (Republic of), Malaysia, Mexico, Netherlands, Peru, Poland, Qatar, Serbia, Slovenia, South Africa, Spain, Thailand, United Arab Emirates, Viet Nam

Angola, Argentina, Armenia, Bahrain, Burundi, Chile, Costa Rica, Czech Republic, Denmark, Egypt, El Salvador, Georgia, Ghana, Greece, Guatemala, Honduras, Hong Kong, Hungary, Indonesia, Iraq, Ireland, Israel, Jordan, Kazakhstan, Kenya, Kuwait, Libya, Malawi, Malaysia, Mauritius, Mexico, Montenegro, Mozambique, New Zealand, Nigeria, Palestinian Territory, Panama, Philippines, Portugal, Qatar, Russian Federation, Rwanda, SAR China, Saudi Arabia, Singapore, Slovenia, South Africa, Taiwan (Republic of China), Tanzania (United Republic of), Trinidad and Tobago, Turkey, Uganda, United Arab Emirates, United Kingdom, United States of America, Uruguay, Viet Nam, Zambia, Zimbabwe

Fluff · Mar 13, 2020

If you create a list like

+Fluff.xlsm

A

B

1

Old

New

2

Macedonia (Republic of)

Macedonia

3

Uae

United Arab Emirates

4

SouthAfrica

South Africa

5

Venezuela (Bolivarian Republic)

Venezuela

List

You can use

VBA Code:

Sub SteveOranjin()
   Dim Ary As Variant
   Dim i As Long
   
   With Sheets("List")
      Ary = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
   End With
   With Sheets("Sheet1").Range("H:H")
      For i = 1 To UBound(Ary)
         .Replace Ary(i, 1), Ary(i, 2), xlPart, , True, , False, False
      Next i
   End With
End Sub

Fluff · Mar 13, 2020

When I copy paste those 2 string into col H, they seem to get corrected quite happily.

It may be that you have some non-breaking spaces in there, what happens if you run this

VBA Code:

   With Sheets("Sheet1").Range("H:H")
      .Replace Chr(160), " ", xlPart, , , , False, False
      .Replace "Macedonia (Republic of)", "Macedonia", xlPart, , True, , False, False
      .Replace "Uae", "United Arab Emirates", xlPart, , True, , False, False
   End With

SteveOranjinSteve · Mar 13, 2020

Now is the away to create an entry box so that I can dynamically choose which column it goes through? I don't want to select the column, because I want the pop up box so I'm careful about which column I select.

Fluff · Mar 14, 2020

Do you want to enter a column number or letter?

SteveOranjinSteve · Mar 16, 2020

VBA Code:

Sub Translate_Country_Name()
    'Variable Names
    Dim Ary As Variant
    Dim i As Long
  
    With Sheets(4)
        Ary = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
    End With
    With Sheets(1).Range("H:H")
        For i = 1 To UBound(Ary)
            .Replace Chr(160), " ", xlPart, , , , False, False
            .Replace Ary(i, 1), Ary(i, 2), xlPart, , True, , False, False
        Next i
    End With
  
End Sub

So that doesn't work either. What am I not seeing here?

Fluff · Mar 16, 2020

Any chance you could answer my question?

SteveOranjinSteve said:
So that doesn't work either.

In what way?

SteveOranjinSteve · Mar 16, 2020

I'm going to try and implement the column selection myself. This is because I want to try and learn.

It appears to run, without finding anything to perform. No errors of any kind are thrown.

Steve

SteveOranjinSteve · Mar 16, 2020

Comparison - 7.3.2.1.xlsm

H

2

JDE Country of Ship To (Alphabetized)

3

Angola, Belgium, Bolivia, Brazil, Bulgaria, Croatia, Greece, Indonesia, Iraq, Israel, Kosovo, Macedonia (Republic of), Malaysia, Mexico, Netherlands, Peru, Poland, Qatar, Serbia, Slovenia, South Africa, Spain, Thailand, United Arab Emirates, Viet Nam

4

Barbados, Brazil, Colombia, Costa Rica, Dominican Republic, Ecuador, El Salvador, Guatemala, Jamaica, Mexico, Nicaragua, Panama, Puerto Rico, Venezuela (Bolivarian Republic)

5

Algeria, Armenia, Belgium, Bosnia and Herzegovina, Brazil, Croatia, Czech Republic, France, Georgia, Greece, Hong Kong, Hungary, Israel, Italy, Kenya, Korea (South), Macedonia (Republic of), Malaysia, Montenegro, Morocco, Peru, Poland, Portugal, Russian Federation, SAR China, Saudi Arabia, Singapore, Slovenia, South Africa, Spain, Sweden, Thailand, Turkey, Ukraine, United Arab Emirates, Uzbekistan

6

Brazil, Colombia, Costa Rica, Ecuador, Malaysia, Mexico, Panama, Puerto Rico, Viet Nam

7

Japan

8

Czech Republic, Hong Kong, Italy, Poland, Russian Federation, SAR China

9

Italy

Original Sheet

So this is how it looks after I have run it.

Comparison - 7.3.2.1.xlsm

A

B

1

Old

New

2

Macedonia(Republic Of)

Macedonia (Republic of)

3

Macedonia (Republic of)

Macedonia

4

UAE

United Arab Emirates

5

SouthAfrica

South Africa

6

Venezuela(BolivarianRepublic)

Venezuela (Bolivarian Republic)

7

Venezuela (Bolivarian Republic)

Venezuela

8

BosniaandHerzegovina

Bosnia and Herzegovina

9

CzechRepublic

Czech Republic

10

HongKong

Hong Kong

11

Korea(South)

South Korea

12

SouthKorea

South Korea

13

RussianFederation

Russian Federation

14

Russian Federation

Russia

15

SaudiArabia

Saudi Arabia

16

SouthAfrica

South Africa

17

CostaRica

Costa Rica

18

PuertoRico

Puerto Rico

19

SaintPierreandMiquelon

Saint Pierre and Miquelon

20

UnitedKingdom

United Kingdom

21

UnitedStatesofAmerica

United States of America

22

SyrianArabRepublic(Syria)

Syrian Arab Republic (Syria)

23

Syrian Arab Republic (Syria)

Syria

24

Tanzania(UnitedRepublicof)

Tanzania (United Republic of)

25

Tanzania (United Republic of)

Tanzania

26

Taiwan(RepublicofChina)

Taiwan (Republic of China)

27

Taiwan (Republic of China)

Taiwan

28

Trinidad and Tobago

List_Do_Not_Touch

That is the sheet I am using to translate the names.

Fluff · Mar 16, 2020

Ok, step through the code using F8 until you get to this line

VBA Code:

    With Sheets(1).Range("H:H")

Then click on View > Locals Window & you should see something like

If you expand Ary you should see your values, similar to

Do you see your search & replace values?

VBA: Why Doesn't this macro do anything?

SteveOranjinSteve

Board Regular

SteveOranjinSteve

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

Fluff

MrExcel MVP, Moderator

SteveOranjinSteve

Board Regular

Fluff

MrExcel MVP, Moderator

SteveOranjinSteve

Board Regular

Fluff

MrExcel MVP, Moderator

SteveOranjinSteve

Board Regular

SteveOranjinSteve

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

VBA: Why Doesn't this macro do anything?

Board Regular

Board Regular

Excel Facts

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock