VBA Boucle recherche et remplace

Sub coder_commune() 
Dim dico As Object 
Dim derlig As Long, cptr As Long 
Dim insee 

Set dico = CreateObject("Scripting.Dictionary") 
With Workbooks("insee.xls").Sheets(1) 
    derlig = .Range("A40000").End(xlUp).Row 
    For cptr = 2 To derlig 
        'nom de la commune colonne A , code Insee colonne E 
        dico.Add UCase(.Cells(cptr, 1).Value), .Cells(cptr, 5).Value 
    Next 
End With 

Application.ScreenUpdating = False 
With Workbooks("mag_brico.xls").Sheets(1) 
     derlig = .Range("F40000").End(xlUp).Row 
     For cptr = 2 To derlig 
        'remplacement du nom de la commune colonne F par le code insee 
        'le nom reste si mauvaise orthographe ou inconnu 
        insee = dico.Item(UCase(.Cells(cptr, 6).Value)) 
        If insee <> "" Then 
            Cells(cptr, 6) = insee 
        End If 
     Next 
End With 
Set dico = Nothing 
End Sub

Option Explicit 

Private Type myTab 
    ville As String 
    insee As Variant 
End Type 

Sub RemplaveVilleParInsee() 
Dim wk As Workbook 
Dim WkWs As Worksheet 
Dim ws As Worksheet 
Dim Derlig As Long 
Dim i As Long 
Dim j As Long 
Dim mTab() As myTab 
Dim ind As Long 

    'On considère que les données sont dans le classeur 
    'mag_brico.xls sur la feuille 1 sinon modifier le numéro de la feuille 
    Set ws = ThisWorkbook.Worksheets(1) 
     
    'On ouvre le classeur insee.xls /!\(chemin à modifier) 
    Set wk = Workbooks.Open("C:\mondossier\insee.xls") 
    'Ou si le classeur est déjà ouvert 
    '// Set wk = Workbooks("C:\mondossier\insee.xls") 
     
    'On considère que les données sont dans le classeur 
    'insee.xls sur la feuille 1 sinon modifier le numéro de la feuille 
    Set WkWs = wk.Worksheets(1) 
     
    'On considère que le nom de villes est dans la colonne A et le n° INSEE dans la colonne F 
    'Le tableau commence ligne 2 
     
    'On met en mémoire (dans la structure mTab() le nom de la ville et n° INSEE associé) 
    Derlig = WkWs.Range("A2").End(xlDown) 
    For i = 2 To Derlig 
    ReDim Preserve mTab(ind)    
        mTab(i).ville = WkWs.Range("A" & i).Value 
        mTab(i).insee = WkWs.Range("F" & i).Value 
        ind = ind + 1 
    Next i 
     
    'On remplace dans mag_brico, le nom de la ville par le n° insee 
    'On considère que la ville se trouve en colonne F et le tableau commence ligne 2 
    Derlig = ws.Range("F2").End(xlDown) 
    For i = LBound(mTab()) To UBound(mTab()) 
        For j = 2 To Derlig 
            If UCase(mTab(i).ville) = UCase(ws.Range("F" & j).Value) Then 
                ws.Range("F" & j).Value = mTab(i).insee 
            End If 
        Next j 
    Next i 
     
    wk.Close (savechanges = False) 
    Set WkWs = Nothing 
    Set wk = Nothing 
    Set ws = Nothing 
       
End Sub

Num = Range("A1").End(xlDown).Offset(0, 5) 

Sub coder_commune()
Dim dico As Object
Dim derlig As Long, cptr As Long
Dim insee

Set dico = CreateObject("Scripting.Dictionary")
With Workbooks("insee.xls").Sheets(1)
    derlig = .Range("A40000").End(xlUp).Row
    For cptr = 2 To derlig
        'nom de la commune colonne A , code Insee colonne C
        dico.Add UCase(.Cells(cptr, 1).Value), .Cells(cptr, 3).Value
    Next
End With

Application.ScreenUpdating = False
With Workbooks("mag_brico.xls").Sheets("magasin")
     derlig = .Range("F40000").End(xlUp).Row
     For cptr = 2 To derlig
        'remplacement du nom de la commune colonne F par le code insee
        'le nom reste si mauvaise orthographe ou inconnu
        insee = dico.Item(UCase(.Cells(cptr, 6).Value))
        If insee <> "" Then
            .Cells(cptr, 6) = insee
        End If
     Next
     .Activate
End With

End Sub