Please hepl !
I have this macro that calculates distance using google maps api.
and it works fine on all pc.s I have tryed except one.
it stops on sort, really stupid sort line .
I can't figure out what could be the problem with this pc it has office 2016 and I also tryed on other pcs with of 2016 and it works.
anyone please...
Sub tablica5()
Dim adresa1, adresa2 As String
Dim U3, U4, U5, ADR As Range
Dim a, b, c, br3, i As Integer
adresa2 = "street x 21, 10150, Zagreb, Croatia"
a = 6
br3 = Application.WorksheetFunction.CountIf(Sheet3.Range("D6:D30"), "<>")
If br3 = 0 Then
Exit Sub
End If
i = 0
For U3 = 1 To br3
For Each ADR In Sheet3.Range(Sheet3.Cells(6 + i, 4), Sheet3.Cells(5 + br3, 4))
adresa1 = ADR & ", Croatia"
"&destinations=" & adresa2 & _
"&mode=driving&departure_time=now&traffic_model=optimistic&key=" & _
"google api key"
WEBs = WorksheetFunction.WebService(XML)
ADR.Offset(0, 2) = Replace(WorksheetFunction.FilterXML(WEBs, "//distance[1]/text"), " km", "")
ADR.Offset(0, 1).Value = "00:" & Replace(Replace(WorksheetFunction.FilterXML(WEBs, "//duration[1]/text"), " min", ":00"), "s", "")
Next ADR
Sheet3.Range(Sheet3.Cells(6 + i, 3), Sheet3.Cells(5 + br3, 7)).Sort Key1:=Sheet3.Range("F" & 6 + i & ":F" & 5 + br3), Order1:=xlAscending, Header:=xlNo
If i = 0 Then
Sheet3.Cells(6 + i, 7) = Sheet3.Cells(3, 4) - Sheet3.Cells(6 + i, 5)
Else:
Sheet3.Cells(6 + i, 7) = Sheet3.Cells((6 + i) - 1, 7) - Sheet3.Cells(6 + i, 5)
End If
adresa2 = Sheet3.Cells(a, 4)
a = a + 1
i = i + 1
Next U3
Sheet3.Range(Sheet3.Cells(6, 3), Sheet3.Cells(5 + br3, 7)).Sort Key1:=Sheet3.Range("G" & 6 & ":G" & 5 + br3), Order1:=xlAscending, Header:=xlNo
Sheet3.Range(Sheet3.Cells(6, 5), Sheet3.Cells(br3 + 5, 5)).Style = "Razlika"
Sheet3.Range(Sheet3.Cells(6, 6), Sheet3.Cells(br3 + 5, 6)).Style = "Ime"
Sheet3.Range(Sheet3.Cells(6, 7), Sheet3.Cells(br3 + 5, 7)).Style = "Vrijeme"
Sheet3.Cells(br3 + 8, 6).Style = "ukupno1"
Sheet3.Cells(br3 + 9, 6).Style = "ukupno1"
Sheet3.Cells(br3 + 9, 7).Style = "ukupno2"
Sheet3.Cells(br3 + 8, 7).Style = "ukupno2"
Sheet3.Cells(br3 + 8, 6) = "Ukupno trajanje putovanja:"
Sheet3.Cells(br3 + 9, 6) = "Ukupno kilometara:"
Sheet3.Cells(br3 + 8, 7) = Application.WorksheetFunction.Sum(Sheet3.Range(Sheet3.Cells(6, 5), Sheet3.Cells(br3 + 5, 5)))
Sheet3.Cells(br3 + 9, 7) = Application.WorksheetFunction.Sum(Sheet3.Range(Sheet3.Cells(6, 6), Sheet3.Cells(br3 + 5, 6))) & " km"
End Sub
