VBA: Why Doesn't this macro do anything?

[SteveOranjinSteve]

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

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

 

[Fluff]

Do you have a list of the old & new values anywhere?

 

[Fluff]

If not you can write your code like

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

 

[SteveOranjinSteve]

If not you can write your code like

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

I was wondering if I could do that.

 

[Fluff]

If you put all the values on a sheet, then you pull them into an array & loop through that. That way it's a lot easier to maintain if anything changes in the future.

 

[SteveOranjinSteve]

If you put all the values on a sheet, then you pull them into an array & loop through that. That way it's a lot easier to maintain if anything changes in the future.

I used to maintain a macro like this, I can't tell you how annoying it was. I used to maintain it for both WHOLE and part.

 

[Fluff]

Do you want to put the two sets of values into a list on a sheet & use that?

 

[SteveOranjinSteve]

If not you can write your code like

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

This still isn't running on the column. When it is done running, "Macedonia (Republic of)" is still "Macedonia (Republic of)".

 

[SteveOranjinSteve]

Do you want to put the two sets of values into a list on a sheet & use that?

Yes, that would be easier, because then I could add values as needed.

 

[Fluff]

Is that in a cell of it's own, or part of a larger string?