Sub Test_2()
Dim Dico As Object, Dico2 As Object, I&, J&, Plg(), Plg2()
Set Dico = CreateObject("Scripting.Dictionary")
Set Dico2 = CreateObject("Scripting.Dictionary")
With Sheets("Feuil1")
Plg = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Value
End With
With Sheets("Feuil2")
Plg2 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Value
For I = LBound(Plg2, 1) To UBound(Plg2, 1)
Dico(Plg2(I, 1)) = Plg2(I, 1)
Next I
For J = LBound(Plg, 1) To UBound(Plg, 1)
If Not Dico.Exists(Plg(J, 1)) Then Dico2(Plg(J, 1)) = "Absent de la liste"
Next J
If Dico2.Count > 0 Then
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(Dico2.Count, 1) = Application.Transpose(Dico2.Keys)
.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Resize(Dico2.Count, 1) = Application.Transpose(Dico2.Items)
MsgBox "Veuillez remplir ce champs avec l'information pertinente. Merci !"
Else
MsgBox "Pas de référence absente"
End If
End With
End Sub