Re Christophe88
with Application.Match
Option Explicit
Sub test()
Dim r As Range, pos, txt As String, e
With Sheets("feuil1").Cells(1).CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
.Columns(3).ClearContents
For Each r In .Columns(1).Cells
pos = Application.Match(r.Value, .Columns(2), 0)
If Not IsNumeric(pos) Then txt = txt & "," & r.Row - 1
Next
For Each e In Split(Mid$(txt, 2), ",")
.Cells(e, 3).Value = .Cells(e, 1).Value
Next
End With
End With
End Sub
Plus simple
Option Explicit
Sub test()
Dim r As Range, pos
With Sheets("feuil1").Cells(1).CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
.Columns(3).ClearContents
For Each r In .Columns(1).Cells
pos = Application.Match(r.Value, .Columns(2), 0)
If Not IsNumeric(pos) Then
.Cells(r.Row - 1, 3).Value = .Cells(r.Row - 1, 1).Value
End If
Next
End With
End With
End Sub
Une autre disposition :
Option Explicit
Sub test()
Dim r As Range, pos, lig As Long
With Sheets("feuil1").Cells(1).CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
.Columns(3).ClearContents
For Each r In .Columns(1).Cells
pos = Application.Match(r.Value, .Columns(2), 0)
If Not IsNumeric(pos) Then
lig = lig + 1
.Cells(lig, 3).Value = .Cells(r.Row - 1, 1).Value
End If
Next
End With
End With
End Sub
klin89