VBA: Why Doesn't this macro do anything?

SteveOranjinSteve

Board Regular
Joined
Nov 18, 2019
Messages
78
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

  1. The Data itself is in a comma separated list. So its like this, "Angola, Belgium, Bolivia, Croatia, Greece, Indonesia" etc... etc...
  2. There are about 134 rows of data or so.
  3. 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

Board Regular
Joined
Nov 18, 2019
Messages
78
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 NamAngola, 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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,082
Office Version
  1. 365
Platform
  1. Windows
If you create a list like
+Fluff.xlsm
AB
1OldNew
2Macedonia (Republic of)Macedonia
3UaeUnited Arab Emirates
4SouthAfricaSouth Africa
5Venezuela (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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,082
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 18, 2019
Messages
78
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,082
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you want to enter a column number or letter?
 

SteveOranjinSteve

Board Regular
Joined
Nov 18, 2019
Messages
78
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

SteveOranjinSteve

Board Regular
Joined
Nov 18, 2019
Messages
78
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Board Regular
Joined
Nov 18, 2019
Messages
78
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Comparison - 7.3.2.1.xlsm
H
2JDE Country of Ship To (Alphabetized)
3Angola, 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
4Barbados, Brazil, Colombia, Costa Rica, Dominican Republic, Ecuador, El Salvador, Guatemala, Jamaica, Mexico, Nicaragua, Panama, Puerto Rico, Venezuela (Bolivarian Republic)
5Algeria, 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
6Brazil, Colombia, Costa Rica, Ecuador, Malaysia, Mexico, Panama, Puerto Rico, Viet Nam
7Japan
8Czech Republic, Hong Kong, Italy, Poland, Russian Federation, SAR China
9Italy
Original Sheet


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

Comparison - 7.3.2.1.xlsm
AB
1OldNew
2Macedonia(Republic Of)Macedonia (Republic of)
3Macedonia (Republic of)Macedonia
4UAEUnited Arab Emirates
5SouthAfricaSouth Africa
6Venezuela(BolivarianRepublic)Venezuela (Bolivarian Republic)
7Venezuela (Bolivarian Republic)Venezuela
8BosniaandHerzegovinaBosnia and Herzegovina
9CzechRepublicCzech Republic
10HongKongHong Kong
11Korea(South)South Korea
12SouthKoreaSouth Korea
13RussianFederationRussian Federation
14Russian FederationRussia
15SaudiArabiaSaudi Arabia
16SouthAfricaSouth Africa
17CostaRicaCosta Rica
18PuertoRicoPuerto Rico
19SaintPierreandMiquelonSaint Pierre and Miquelon
20UnitedKingdomUnited Kingdom
21UnitedStatesofAmericaUnited States of America
22SyrianArabRepublic(Syria)Syrian Arab Republic (Syria)
23Syrian Arab Republic (Syria)Syria
24Tanzania(UnitedRepublicof)Tanzania (United Republic of)
25Tanzania (United Republic of)Tanzania
26Taiwan(RepublicofChina)Taiwan (Republic of China)
27Taiwan (Republic of China)Taiwan
28Trinidad and TobagoTrinidad and Tobago
List_Do_Not_Touch


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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,082
Office Version
  1. 365
Platform
  1. Windows
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
1584388982091.png


If you expand Ary you should see your values, similar to
1584389045081.png

Do you see your search & replace values?
 

Forum statistics

Threads
1,136,642
Messages
5,676,960
Members
419,665
Latest member
allsmilees2

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