XL 2013 RECHERCHE AGENCE LA PLUS PROCHE D'UNE COMMUNE

Option Explicit

Sub Recherche_Agence()
    Dim f1 As Worksheet, f2 As Worksheet
    Dim DerLig_f1 As Long, DerLig_f2 As Long, i As Long, k As Long
    Dim Dif_Lat As Long, Dif_Lon As Long
    Dim Agence As String, Result As String, Result_Inter As String
    Dim Lon_Com As String, Lat_Com As String
    Dim Lon_Ag As String, Lat_Ag As String
    Dim Deb As Double
    
    Application.ScreenUpdating = False
    Deb = Timer
    Set f1 = Sheets("COMMUNE")
    Set f2 = Sheets("AGENCES")
    DerLig_f1 = f1.Range("A" & Rows.Count).End(xlUp).Row
    DerLig_f2 = f2.Range("A" & Rows.Count).End(xlUp).Row
    f1.Range("M2:M" & DerLig_f1).ClearContents
    For i = 2 To DerLig_f1
        Lat_Com = Val(f1.Cells(i, "C"))
        Lon_Com = Val(f1.Cells(i, "D"))
        Result = 1000
        For k = 2 To DerLig_f2
            Lat_Ag = Val(f2.Cells(k, "K"))
            Lon_Ag = Val(f2.Cells(k, "L"))
            Dif_Lat = Abs(Lat_Ag - Lat_Com) * 1
            Dif_Lon = Abs(Lon_Ag - Lon_Com) * 1
            
            Result_Inter = Sqr(Dif_Lat * Dif_Lat + Dif_Lon * Dif_Lon)
            If Result_Inter < Result Then
                Result = Result_Inter
                Agence = f2.Cells(k, "A")
            End If
        Next k
        f1.Cells(i, "M") = Agence
    Next i
    
    MsgBox "Temps d'exécution: " & Round(Timer - Deb, 2) & "Sec"
    Set f1 = Nothing
    Set f2 = Nothing
End Sub