Sub test()
Dim PlageAncListe, PlageNvelleListe, AncListe, NvelleListe, i&, j&, Résultat&, Diff, test
Set PlageAncListe = Sheets("Feuil1").Range("A4", Range("A" & Rows.Count).End(xlUp))
Set PlageNvelleListe = Sheets("Feuil1").Range("B4", Range("B" & Rows.Count).End(xlUp))
AncListe = Sheets("Feuil1").Range("A4", Range("A" & Rows.Count).End(xlUp)).Value
NvelleListe = Sheets("Feuil1").Range("B4", Range("B" & Rows.Count).End(xlUp)).Value
Diff = Application.Evaluate("COUNTA(" & PlageAncListe.Address _
& ")- SUM(COUNTIF(" & PlageAncListe.Address & "," & PlageNvelleListe.Address & "))")
For i = 1 To PlageNvelleListe.Rows.Count
Dim tabl()
ReDim Preserve tabl(1 To PlageNvelleListe.Rows.Count)
On Error Resume Next
Résultat = Application.WorksheetFunction.Match(NvelleListe(i, 1), AncListe, 0)
If Err.Number <> 0 Then
tabl(i) = ""
Else
tabl(i) = AncListe(Résultat, 1)
End If
Next i
test = Application.WorksheetFunction.CountA(tabl)
j = 1
For i = 1 To PlageAncListe.Rows.Count
Résultat = Application.WorksheetFunction.CountIf(PlageNvelleListe, AncListe(i, 1))
If Résultat = 0 Then
ReDim Preserve tabl(1 To PlageAncListe.Rows.Count + Diff)
tabl(j + test) = AncListe(i, 1)
j = j + 1
End If
Next i
[E4].Resize(UBound(tabl)) = Application.Transpose(tabl)
[F4].Resize(PlageAncListe.Rows.Count) = NvelleListe
End Sub