Hi Experts,

My level of coding is not sufficient to get this done. Hence, need your help please

I have two tables.

Table.MissingCityCode is a list of location with coordinates (Lat/Lon) but no CityCode

Table.CityCodeReference is a list of known reference with coordinates

What I would like to do, is to have a VBA code that do the following:

For each country code from Table.MissingCityCode

- Look at each row, take the CountryCode, Lat/Long values (say this is point_A)

- Go to Table.CityCodeReference and filter only for the matched country code

- Calculated the distance between point_A and each available row of City (in kilometers)

- Find the nearest one, and take the value from Table.CityCodeReference.Ci

tyCode

- Update Table.MissingCityCode.MissingCityCode with value found from above

- Loop for next city in a given countrycode

- Loop for the next countryCode

Please see the sample as attached.

NOTE:

From research, I know the excel formula to calculate distance between two coordinates is

**=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371**

where 6371 is Earth radius in KM.

But, to put the loop and find nearest value as needed here, it is a bit out of my reach.

EE-Samples.xlsx
much calcs from other sources.

User defined functions go in a regular module sheet:

Open in new window

Macro and another user defined function also go in a regular module sheet:

Open in new window

Coord-Sample1.xlsm