Excel 2007 VBA: RechercheV

Private Const fich2 = "D:\CCM\temp\data\test2" 

Sub searchDonnees() 
Dim wk2 As Workbook 
Dim ws1 As Worksheet 
Dim ws2 As Worksheet 
Dim derlig1 As Long 
Dim derlig2 As Long 
Dim cel1 
Dim cel2 

    Set wk2 = Workbooks.Open(fich2) 
    Set ws2 = wk2.Worksheets(1) 
    derlig2 = ws2.Range("N65536").End(xlUp).Row 
     
    Set ws1 = ThisWorkbook.Worksheets(1) 
    derlig1 = ws1.Range("BK65536").End(xlUp).Row 
     
    For Each cel1 In ws1.Range("BK2:BK" & derlig1) 
        For Each cel2 In ws2.Range("N2:N" & derlig2) 
            If cel1.Value = cel2.Value Then 
                ws1.Range("BN" & cel1.Row).Value = ws2.Range("L" & cel2.Row).Value 
            End If 
        Next cel2 
    Next cel1 

Set wk2 = Nothing
Set ws2 = Nothing
Set ws1 = Nothing
     
End Sub