SteveOranjinSteve
Board Regular
- Joined
- Nov 18, 2019
- Messages
- 78
- Office Version
- 365
- 2019
- Platform
- 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:
Steve
I've tried the following:
- Changing the match case to false.
- Remove the comma between replace and LookAt.
- 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.
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