#### Kevin8819

##### New Member
Hi,

I am looking for a way to find the closest destination based on a post code entered by an user. As below, when an user inputs "Post code A", I would like the macro to use maps and bring back the closest location to post code A which for e.g may be Post code Y

Post code entered : Post code A

List of Possible matches:
Post code X
Post code Y
Post code Z

Expected result

Closest Location: Post code Y

Any pointers will be great!

Thanks
Kevin

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### kweaver

##### Well-known Member
How do you determine "closest" ? Are you comparing X with several other codes?
I think you need the Google Maps API for this.

Last edited:

#### kweaver

##### Well-known Member
I found this app that I did years ago for a client. Maybe this function and info will help.

A column (starting in A3) has zips "from" and row 2 has zips "to" (starting in B3).

B3, for example, has this code:

Code:
``=IFERROR(3960*CentralAngle(VLOOKUP(\$A3,zipinfo!\$C\$2:\$E\$33248,2,FALSE),VLOOKUP(\$A3,zipinfo!\$C\$2:\$E\$33248,3,FALSE),VLOOKUP(B\$2,zipinfo!\$C\$2:\$E\$33248,2,FALSE),VLOOKUP(B\$2,zipinfo!\$C\$2:\$E\$33248,3,FALSE)),"")``
which is filled across and down.

This function does the trigonometry:

Code:
``````Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
ByVal lat2 As Double, ByVal lon2 As Double) As Double
' shg 2008-1111

' Returns central angle between two point in RADIANS
' using Vincenty formula

Const pi    As Double = 3.14159265358979
Const D2R   As Double = pi / 180#

Dim dLon    As Double
Dim x       As Double
Dim y       As Double

' convert angles from degrees to radians
lat1 = D2R * lat1
lon1 = D2R * lon1
lat2 = D2R * lat2
lon2 = D2R * lon2

dLon = lon2 - lon1  ' delta lon

x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
CentralAngle = WorksheetFunction.Atan2(x, y)
End Function``````
This works for the United States...probably something could be adopted for the UK or elsewhere with Post Codes unlike the US.

Just need the long. and lat. to make the calculations work.

#### Kevin8819

##### New Member
I found this app that I did years ago for a client. Maybe this function and info will help.

A column (starting in A3) has zips "from" and row 2 has zips "to" (starting in B3).

B3, for example, has this code:

Code:
``=IFERROR(3960*CentralAngle(VLOOKUP(\$A3,zipinfo!\$C\$2:\$E\$33248,2,FALSE),VLOOKUP(\$A3,zipinfo!\$C\$2:\$E\$33248,3,FALSE),VLOOKUP(B\$2,zipinfo!\$C\$2:\$E\$33248,2,FALSE),VLOOKUP(B\$2,zipinfo!\$C\$2:\$E\$33248,3,FALSE)),"")``
which is filled across and down.

This function does the trigonometry:

Code:
``````Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
ByVal lat2 As Double, ByVal lon2 As Double) As Double
' shg 2008-1111

' Returns central angle between two point in RADIANS
' using Vincenty formula

Const pi    As Double = 3.14159265358979
Const D2R   As Double = pi / 180#

Dim dLon    As Double
Dim x       As Double
Dim y       As Double

' convert angles from degrees to radians
lat1 = D2R * lat1
lon1 = D2R * lon1
lat2 = D2R * lat2
lon2 = D2R * lon2

dLon = lon2 - lon1  ' delta lon

x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
CentralAngle = WorksheetFunction.Atan2(x, y)
End Function``````
This works for the United States...probably something could be adopted for the UK or elsewhere with Post Codes unlike the US.

Just need the long. and lat. to make the calculations work.
Thanks for this. I will try this one and come back to you. Also, the link that you shared in the PM takes me to a 404 page. (Sorry, I am not able to DM you)

#### Kevin8819

##### New Member
How do you determine "closest" ? Are you comparing X with several other codes?
I think you need the Google Maps API for this.
Comparing (1)"Post code A" -(Dynamic) with a list of (2)Static post codes to arrive at the shortest distance between (1) and (2)

#### Michael M

##### Well-known Member
It's only a link to another MrExcel thread, shouldn't get a 404

Code:
``https://www.mrexcel.com/forum/excel-questions/587230-excel-calculate-distance-miles-between-post-codes.html``
Maybe the full address will help

Last edited: