Permanent Reference Table

[kelseye2eye]

Hi!

I run multiple reports that give me data with the a country code rather than country name. I have a table of country codes and country names that I have referenced using a VLOOKUP, but was wondering what the most efficient way would be to save the Country Code table as an Add-On and run an automated replacement based upon the codes?

Any insight as to how to begin this process would be fantastic, as I'm stuck on a find/replace for each country code, but I know there has to be a better way!

Thanks!
Kelsey

 

[Damon Ostrander]

Hi Kelsey,

Here is what I would recommend.

In a new workbook (that will become an add-in workbook) insert this code in a new macro module:

Function CountryName(CountryCode As String) As String
   With Sheet1
      CountryName = .Application.WorksheetFunction.VLookup(CountryCode, .Range("CCtable"), 2)
   End With
End Function

Then on Sheet1 of the new workbook paste the table of Country Name vs Country Code such that the country code is in column A and the country name is in column B. If the country codes are not in alphabetical order, sort them.

Select the table, not including any header rows. Name the table "CCtable" by entering this name in the Name box just above cell A1.

Now you have available a user-defined function to look up country name. You can use it from a cell like this:

=countryname("SK")

which should yield "Switzerland".

You can then save this workbook as an add-in (.xlam) file, and then load it as an add-in in Excel to make the CountryName function available to other workbooks. Don't forget to save it as an Excel file (.xlsm) first so you retain a copy of it as once converted to an add-in you lose its source code.

Good luck!

Damon