1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| Sub test()
Dim c As Range, Plage As Range, Ligne As Long, Libs, Nbrs
Libs = Array("reçu", "demandé", "pris ST", "livré CIS")
With Sheets("Feuil1")
Set Plage = .Range(.[D3], .Cells(.Rows.Count, 4).End(xlUp))
End With
With Sheets("Feuil2")
For Each c In Plage
If c.Offset(, 1) = "" Then
Ligne = Ligne + 1
decal = (Application.Match(c.Value, Libs, 0) - 1) * -1
.Cells(Ligne, 1).Value = c.Offset(decal, -3)
.Cells(Ligne, 2).Value = c.Offset(decal, -2)
.Cells(Ligne, 3).Value = c.Offset(decal, -1)
.Cells(Ligne, 4).Value = c
End If
Next c
End With
End Sub |
Partager