Excel vba sort error on one pc with office 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-1492740%22%20slang%3D%22en-US%22%3EExcel%20vba%20sort%20error%20on%20one%20pc%20with%20office%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492740%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22x-hidden-focus%22%3EPlease%20hepl%20!%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3EI%20have%20this%20macro%20that%20calculates%20distance%20using%20google%20maps%20api.%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3Eand%20it%20works%20fine%20on%20all%20pc.s%20I%20have%20tryed%20except%20one.%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3Eit%20stops%20on%20sort%2C%20really%20stupid%20sort%20line%20.%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3EI%20can't%20figure%20out%20what%20could%20be%20the%20problem%20with%20this%20pc%20it%20has%20office%202016%20and%20I%20also%20tryed%20on%20other%20pcs%20with%20of%202016%20and%20it%20works.%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3Eanyone%20please...%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3ESub%20tablica5()%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EDim%20adresa1%2C%20adresa2%20As%20String%3C%2FDIV%3E%3CDIV%3EDim%20U3%2C%20U4%2C%20U5%2C%20ADR%20As%20Range%3C%2FDIV%3E%3CDIV%3EDim%20a%2C%20b%2C%20c%2C%20br3%2C%20i%20As%20Integer%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3Eadresa2%20%3D%20%22street%20x%2021%2C%2010150%2C%20Zagreb%2C%20Croatia%22%3C%2FDIV%3E%3CDIV%3Ea%20%3D%206%3C%2FDIV%3E%3CDIV%3Ebr3%20%3D%20Application.WorksheetFunction.CountIf(Sheet3.Range(%22D6%3AD30%22)%2C%20%22%26lt%3B%26gt%3B%22)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20If%20br3%20%3D%200%20Then%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20Exit%20Sub%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20End%20If%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3Ei%20%3D%200%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EFor%20U3%20%3D%201%20To%20br3%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20For%20Each%20ADR%20In%20Sheet3.Range(Sheet3.Cells(6%20%2B%20i%2C%204)%2C%20Sheet3.Cells(5%20%2B%20br3%2C%204))%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20adresa1%20%3D%20ADR%20%26amp%3B%20%22%2C%20Croatia%22%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20XML%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2Fmaps.googleapis.com%2Fmaps%2Fapi%2Fdistancematrix%2Fxml%3Forigins%3D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fmaps.googleapis.com%2Fmaps%2Fapi%2Fdistancematrix%2Fxml%3Forigins%3D%3C%2FA%3E%22%20%26amp%3B%20adresa1%20%26amp%3B%20_%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22%26amp%3Bdestinations%3D%22%20%26amp%3B%20adresa2%20%26amp%3B%20_%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22%26amp%3Bmode%3Ddriving%26amp%3Bdeparture_time%3Dnow%26amp%3Btraffic_model%3Doptimistic%26amp%3Bkey%3D%22%20%26amp%3B%20_%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22google%20api%20key%22%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20WEBs%20%3D%20WorksheetFunction.WebService(XML)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20ADR.Offset(0%2C%202)%20%3D%20Replace(WorksheetFunction.FilterXML(WEBs%2C%20%22%2F%2Fdistance%5B1%5D%2Ftext%22)%2C%20%22%20km%22%2C%20%22%22)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20ADR.Offset(0%2C%201).Value%20%3D%20%2200%3A%22%20%26amp%3B%20Replace(Replace(WorksheetFunction.FilterXML(WEBs%2C%20%22%2F%2Fduration%5B1%5D%2Ftext%22)%2C%20%22%20min%22%2C%20%22%3A00%22)%2C%20%22s%22%2C%20%22%22)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20Next%20ADR%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Sheet3.Range(Sheet3.Cells(6%20%2B%20i%2C%203)%2C%20Sheet3.Cells(5%20%2B%20br3%2C%207)).Sort%20Key1%3A%3DSheet3.Range(%22F%22%20%26amp%3B%206%20%2B%20i%20%26amp%3B%20%22%3AF%22%20%26amp%3B%205%20%2B%20br3)%2C%20Order1%3A%3DxlAscending%2C%20Header%3A%3DxlNo%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20If%20i%20%3D%200%20Then%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Sheet3.Cells(6%20%2B%20i%2C%207)%20%3D%20Sheet3.Cells(3%2C%204)%20-%20Sheet3.Cells(6%20%2B%20i%2C%205)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Else%3A%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Sheet3.Cells(6%20%2B%20i%2C%207)%20%3D%20Sheet3.Cells((6%20%2B%20i)%20-%201%2C%207)%20-%20Sheet3.Cells(6%20%2B%20i%2C%205)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20If%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20adresa2%20%3D%20Sheet3.Cells(a%2C%204)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20a%20%3D%20a%20%2B%201%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20i%20%3D%20i%20%2B%201%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ENext%20U3%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%20color%3D%22%23FF0000%22%3ESheet3.Range(Sheet3.Cells(6%2C%203)%2C%20Sheet3.Cells(5%20%2B%20br3%2C%207)).Sort%20Key1%3A%3DSheet3.Range(%22G%22%20%26amp%3B%206%20%26amp%3B%20%22%3AG%22%20%26amp%3B%205%20%2B%20br3)%2C%20Order1%3A%3DxlAscending%2C%20Header%3A%3DxlNo%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ESheet3.Range(Sheet3.Cells(6%2C%205)%2C%20Sheet3.Cells(br3%20%2B%205%2C%205)).Style%20%3D%20%22Razlika%22%3C%2FDIV%3E%3CDIV%3ESheet3.Range(Sheet3.Cells(6%2C%206)%2C%20Sheet3.Cells(br3%20%2B%205%2C%206)).Style%20%3D%20%22Ime%22%3C%2FDIV%3E%3CDIV%3ESheet3.Range(Sheet3.Cells(6%2C%207)%2C%20Sheet3.Cells(br3%20%2B%205%2C%207)).Style%20%3D%20%22Vrijeme%22%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ESheet3.Cells(br3%20%2B%208%2C%206).Style%20%3D%20%22ukupno1%22%3C%2FDIV%3E%3CDIV%3ESheet3.Cells(br3%20%2B%209%2C%206).Style%20%3D%20%22ukupno1%22%3C%2FDIV%3E%3CDIV%3ESheet3.Cells(br3%20%2B%209%2C%207).Style%20%3D%20%22ukupno2%22%3C%2FDIV%3E%3CDIV%3ESheet3.Cells(br3%20%2B%208%2C%207).Style%20%3D%20%22ukupno2%22%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ESheet3.Cells(br3%20%2B%208%2C%206)%20%3D%20%22Ukupno%20trajanje%20putovanja%3A%22%3C%2FDIV%3E%3CDIV%20class%3D%22x-hidden-focus%22%3ESheet3.Cells(br3%20%2B%209%2C%206)%20%3D%20%22Ukupno%20kilometara%3A%22%3C%2FDIV%3E%3CDIV%3ESheet3.Cells(br3%20%2B%208%2C%207)%20%3D%20Application.WorksheetFunction.Sum(Sheet3.Range(Sheet3.Cells(6%2C%205)%2C%20Sheet3.Cells(br3%20%2B%205%2C%205)))%3C%2FDIV%3E%3CDIV%3ESheet3.Cells(br3%20%2B%209%2C%207)%20%3D%20Application.WorksheetFunction.Sum(Sheet3.Range(Sheet3.Cells(6%2C%206)%2C%20Sheet3.Cells(br3%20%2B%205%2C%206)))%20%26amp%3B%20%22%20km%22%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EEnd%20Sub%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1492740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor
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
 
0 Replies