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
- Update Table.MissingCityCode.Miss
e with value found from above
- Loop for next city in a given countrycode
- Loop for the next countryCode
Please see the sample as attached.
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.