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
| Sub Test()
Dim FL1 As Worksheet, FL2 As Worksheet, Cell As Range, c As Range
Dim NoLig As Long, Cpteur As Integer
Set FL1 = Worksheets("feuil1")
Set FL2 = Worksheets("feuil2")
NoLig = 2
Do
Set Cell = FL1.Cells(NoLig, 1)
With FL2.Range("a1:a" & FL2.Range("a1:a" & Cells(Columns(1).Cells.Count, 1).End(xlUp).Row))
Set c = .Find(Cell, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If Cpteur > 0 Then
'insert une ligne si plusieurs items identiques
Cell.EntireRow.Insert shift:=xlShiftDown
End Sub
Cell.Offset(Cpteur, 0) = c 'copie la 1ère colonne
Cell.Offset(Cpteur, 1) = c.Offset(0, 1) '... la seconde
Set c = .FindNext(c)
Cpteur = Cpteur + 1
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
NoLig = Cell.row + Cpteur
Cpteur = 0
Loop While NoLig <= FL1.Cells(Columns(1).Cells.Count, 1).End(xlUp).Row
End Sub |
Partager