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]

I would also suggest changing things like

+Fluff.xlsm
	A	B
1	Old	New
2	Macedonia(Republic Of)	Macedonia (Republic of)
3	Macedonia (Republic of)	Macedonia
List

to

+Fluff.xlsm
	A	B
1	Old	New
2	Macedonia(Republic Of)	Macedonia
3	Macedonia (Republic of)	Macedonia
List

And also sort it on col A A-Z

 

[SteveOranjinSteve]

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

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

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

If you expand Ary you should see your values, similar to
View attachment 9094
Do you see your search & replace values?

It looks like it to me.

 

[Fluff]

Ok, that's fine as it's correctly picking up the array.
I would suggest doing a search & replace on the list to replace any Chr(160) with a space, just in-case that's the problem.

 

[SteveOranjinSteve]

Finally! Got it working!

 

[Fluff]

Glad to hear it & thanks for the feedback