Calculate Distance between Zip Codes

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,245
Office Version
  1. 365
Platform
  1. Windows
Dear Smartest Exclers In The World,

I have a table with row headers as source zip code and column headers as destination zip codes and I would like to calculate the distance between the two zip codes. Does anyone know how to do that, maybe a User Defined function or something?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I have find easy idea for your requirement but not attached file option in your system.

PFA
 
Last edited by a moderator:
Upvote 0
Hi,

I am also trying to calculate the distance between 2 places in excel.
I have tried the VBA code.
Could is also be the google API code?

I always get 0 or -1 (I tried 2 different codes)
[COLOR=rgba(0, 0, 0, 0.87)]


[/COLOR]​
 
Upvote 0
ok, indeed. I just checked again and now the API is ok I think, but the formula is still not working.
the errorhandle takes over every time.
Any suggestion?
Can it be the language? Or the way my data is formed?

Public Function GetDistance(start As String, dest As String)

Dim firstVal AsString, secondVal As String, lastVal As String

firstVal ="http://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal ="&destinations="
lastVal ="&mode=car&sensor=false&key=google API key "

Set objHTTP =CreateObject("MSXML2.ServerXMLHTTP")
URL = firstVal& Replace(start, " ", "+") & secondVal &Replace(dest, " ", "+") & lastVal

objHTTP.Open"GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0(compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send("")
If InStr(objHTTP.responseText,"""distance"" : {") = 0 Then GoTo ErrorHandl
Set RegEx =CreateObject("VBScript.RegExp"): RegEx.Pattern ="""value"".*?([0-9]+)": RegEx.Global = False
Set matches =RegEx.Execute(objHTTP.responseText)
tmpVal = Replace(matches(0).SubMatches(0),".", Application.International(xlListSeparator))
GetDistance =CDbl(tmpVal)
Exit Function
ErrorHandl:
GetDistance = -1
End Function
 
Upvote 0
:confused:
Not sure. I was recently upgraded to 2016 at work and is currently not working either.
Could be a couple of things for sure....
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top