VBA Country code from Cell Range

[farid121]

I import a mailing list of customers from my website which need to be formatted to be accepted by the Royal Mail for import in to their website.
Selected cells will be deleted as per their requirement but two additional cells need to be created to complete the file ready for export.

1. A list of country names in a cell which I need to match with country codes ( a list provided by Royal Mail ) and extract them to a new cell. How do you look up country codes every time you want to repeat this task every day. Do you create a hard code in VBA for each country with their matching code and is there a easy way to create this list programmatically.
2. A second cell would be the service which is based on where the item is being posted. EG if the item is posted in the UK the service code would be TPS and if its Europe it would be OLA etc. These codes are fixed to UK, selected countries for Europe and Rest Of the World.

The code should run for cell count in row A1 and extraction should be in row h and row I
Thanks in advance

[Teeroy]

You don't need VBA for either of these. Have a second sheet for the country name, code and service list and use the VLOOKUP worksheet formula.