# Excel Calculate Miles from Zip Code to Zip Code

#### VballRef

##### New Member
I am aware that there are other similar questions like this on here, but they seem to be pieced together, and I cannot seem to find a single answer that can be easily deployed. I previously used G_Distance function to calculate miles between a matrix of zip codes, and it worked flawlessly. I now have obtained a Google API to be able to achieve the same result, but I have been unable to get it to work within my current Excel framework.

Basically, I'm looking for a formula (or macro) using a Google API that will allow the mileage to be calculated as shown below (do not need the actual "mi" suffix--just there to show what I'm calculating).

 61820 47408 52242 20742 48104 Name1 32220 ??mi ??mi ??mi ??mi ??mi Name2 75501 ??mi ??mi ??mi ??mi ??mi Name3 31316 ??mi ??mi ??mi ??mi ??mi

<tbody>
</tbody>

I know this is possible...and I'm weary of trying to figure it out! I'm hoping some of my Excel friends can help me!

Thanks!

### 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.

#### VballRef

##### New Member
I finally got the code to work!! Using the code below, I am able to get the result in meters...so the only thing I still need help with is converting to miles. Is there any easy way to build that conversion into this code? Or do I need a separate function for that?

Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
secondVal = "+&destinations="
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

#### VballRef

##### New Member
Sorry for the duplication...I cannot seem to edit my post. I was clearly over-thinking this . The conversion part was easy...no need to add to the code! Just used the simple formula: =(CONVERT(GetDistance(C\$1,\$B2),"m","mi"))

Replies
4
Views
455
Replies
4
Views
1K
Replies
7
Views
1K
Replies
1
Views
8K
Replies
2
Views
302

1,129,574
Messages
5,637,162
Members
416,959
Latest member
Mohzein

### 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.

### Which adblocker are you using?

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

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