I'm missing something obvious here and I just can't figure out how to quite go about it.

I've essentially got a list of 20 locations - then looking up their zip codes and referencing them to lat/long.

I want to take the 1st location (marking it as 1) and find the next closest location from it of the 19 remaining (and mark that location as 2). Then find the next closest location to location #2 and mark that as #3, etc. More or less trying to find the shortest distance path that taking me to all locations on that list without repeating. Short example below.

Good on finding the distance and comparing it, my issue is more with how to knock a location off from the list once it's been selected.

Any assistance would be appreciated it.

For R = 1 To 1
    CloseDistance = 1000
    LocBase = Cells(3, region * 4 - 3)
    Cells(3, region * 4) = 1
    For x = 3 To LastRow
        lat1 = Application.WorksheetFunction.VLookup(LocBase, Sheets("Loc Info").Range("B:AZ"), 32, False) * D2R
        lon1 = Application.WorksheetFunction.VLookup(LocBase, Sheets("Loc Info").Range("B:AZ"), 33, False) * D2R
        For Z = 3 To LastRow
            MsgBox Cells(Z, R * 4)
            If Cells(Z, R * 4) = "" Then
                TestLoc = Cells(Z, R * 4 - 3)
            End If
            lat2 = Application.WorksheetFunction.VLookup(TestLoc, Sheets("Loc Info").Range("B:AZ"), 32, False) * D2R
            lon2 = Application.WorksheetFunction.VLookup(TestLoc, Sheets("Loc Info").Range("B:AZ"), 33, False) * D2R
            distancexformula = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(lon2 - lon1)
            distanceyformula = Sqr((Cos(lat2) * Sin(lon2 - lon1)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(lon2 - lon1)) ^ 2)
            Distance = WorksheetFunction.Atan2(distancexformula, distanceyformula) * 3958.75587
            If Distance < CloseDistance Then
                CloseLoc = TestLoc
                CloseDistance = Distance
                LocRow = Z
                'MsgBox Distance & " - " & LocBase & " - " & TestLoc
            End If
        Next Z
        LocBase = CloseLoc
        Cells(LocRow, R * 4) = x - 2
    Next x
Next R
More or less the X loop doesn't do anything - I correctly find the location closest to Alpha but don't go beyond that because it just gets stuck there.