Forum Discussion
Excel vba sort error on one pc with office 2016
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"
XML = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & adresa1 & _
"&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
1 Reply
I know this problem was a very long time ago, however I had this error at my work and in case someone else needs the answer.
The problem is that in Excel 2016 there is no sort Key#. Delete the number after the key and it should work on Excel 2016.
Sheet3.Range(Sheet3.Cells(6, 3), Sheet3.Cells(5 + br3, 7)).Sort Key1:=Sheet3.Range("G" & 6 & ":G" & 5 + br3), Order1:=xlAscending, Header:=xlNo
