Help with VBA Code - need answer not in MsgBox but into a Cell - pullin' my last two hairs off my head

[Mark_Weir]

I downloaded this VBA to help with mapping in Google for mileage from one customer to another.

Simple enough - sorta.

It works - works really well.
It works in Meters - not miles (Sorry I am in the US) - but I can deal with that.

Currently, it gives the output to a MsgBox; and I would love to just have it appear in a cell so I can write a formula to convert meters to miles etc...

Any help would be appreciated....

This is the Sub to give me Answer (I push Button on spreadsheet - viola it works in meters)

Sub TestDistance()
MsgBox GetDistance([DIST_FROM], [DIST_TO]) & " meters"




End Sub
Sub TestDuration()
MsgBox GetDuration([DUR_FROM], [DUR_TO]) & " seconds"
End Sub

This is the VBA for Google Mapping / Distance....
Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal = "&destinations="
lastVal = "&mode=car&language=pl&sensor=false&key=" & Range("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

Any help - Thanks
Mark

 

[rlv01]

Sub TestDistance()
    Dim Distance As String

    Distance = GetDistance([DIST_FROM], [DIST_TO]) & " meters"
    Range("A1").Value = Distance
End Sub

 

[Mark_Weir]

Sub TestDistance()
    Dim Distance As String

    Distance = GetDistance([DIST_FROM], [DIST_TO]) & " meters"
    Range("A1").Value = Distance
End Sub

Ugh- that simple.
THANKS
MARK