1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| Sub test()
'Note : Application.Match correspond à la fonction EQUIV
Dim rngBF1 As Range, rngBF2 As Range, cell As Range
Dim retour As Variant
Dim temp As Variant, i As Long
i = 1
Set rngBF1 = Sheets("Feuil1").Range("A1:" & Sheets( _
"Feuil1").Cells(Rows.Count, 1).End(xlUp).Address)
Set rngBF2 = Sheets("Feuil2").Range("A1:" & Sheets( _
"Feuil2").Cells(Rows.Count, 1).End(xlUp).Address)
ReDim temp(1 To rngBF1.Count)
For Each cell In rngBF1
If Not IsError(Application.Match(cell, rngBF2, 0)) Then
retour = Application.Match(cell, rngBF2, 0)
temp(i) = Sheets("Feuil2").Cells(retour, 2).Value
Else
temp(i) = "sans correspondance"
End If
i = i + 1
Next
Sheets("Feuil1").Range("B1").Resize(i, 1).Value = Application.Transpose(temp)
End Sub |
Partager