I have a forumla that uses bing maps to get the distance between two sets of coordinates. My 'site' coordinates are in column B (800+) and my 'technician' coordinates are in row 3. I also have my bing key in A2.
I wrote the code so that I can enter formula =getdistance(Site coordinates, Technician coordinates, Key) and it will pull the coordinates based on those. When I put formula in row 4 to get distance from each site to the technician, I get the #VALUE! error in about 500 of my 800 rows. I know the formula is correct and works because if I click into each cell it will recalculate and show the correct number.
Also if I screw up and do not do a copy/paste values - sometimes the sheet will recalculate and I will lose some that had previously worked. I've searched and do not see another issue like this anywhere. My internet connection is great and I have even plugged directly into the modem instead of using wifi.
View attachment 51388
This is the code I wrote is setting in the Module of visual basic
:
Public Function GetDistance(start As String, dest As String, key As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
secondVal = "&destinations="
lastVal = "&travelMode=driving&o=xml&key=" & key & "&distanceUnit=mi"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = firstVal & start & secondVal & dest & lastVal
objHTTP.Open "GET", Url, False
objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.Send ("")
GetDistance = Round(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.515, 0)
End Function
I wrote the code so that I can enter formula =getdistance(Site coordinates, Technician coordinates, Key) and it will pull the coordinates based on those. When I put formula in row 4 to get distance from each site to the technician, I get the #VALUE! error in about 500 of my 800 rows. I know the formula is correct and works because if I click into each cell it will recalculate and show the correct number.
Also if I screw up and do not do a copy/paste values - sometimes the sheet will recalculate and I will lose some that had previously worked. I've searched and do not see another issue like this anywhere. My internet connection is great and I have even plugged directly into the modem instead of using wifi.
View attachment 51388
This is the code I wrote is setting in the Module of visual basic
:
Public Function GetDistance(start As String, dest As String, key As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
secondVal = "&destinations="
lastVal = "&travelMode=driving&o=xml&key=" & key & "&distanceUnit=mi"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = firstVal & start & secondVal & dest & lastVal
objHTTP.Open "GET", Url, False
objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.Send ("")
GetDistance = Round(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.515, 0)
End Function