How do I quickly re-map values with VBA?


Do you remember the 7 most common data integrity problems?

4 of them needed business expertise and 3 of them were immediately solvable.

If you only have Excel in your toolkit, here’s a quick way in which you can address them in a relatively painless way:

(2) same person, different spellings

(e.g. TENEVA ANGELINA in 1 system vs. Teneva, Angelina in another)

(3) use of different names

(e.g. Nederland in some record entries vs. Netherlands in others; Great Britain in 1 system vs. United Kingdom in another)

Sub ReplaceConstantValues()
Dim Cell As Range
For Each Cell In ActiveSheet.Range("E2:E" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)
Cell.Replace " USA", "USA"
If Cell.Value = "Nederland" Then Cell.Value = "Netherlands"
If Cell.Value = "Luxemburg" Then Cell.Value = "Luxembourg"
Next Cell
End Sub

Happy VBA coding!