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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| Sub replan()
Dim w As Integer, verif As Date
I = 0
Dl = 150 'un changement
For Each Ws In Worksheets
If Ws.Name Like "Prévi*" Then
For y = 6 To Dl
For x = 11 To 51 Step 10
With Ws
If IsDate(.Cells(y, x - 2)) Then
verif = .Cells(y, x - 2)
If UCase(.Cells(y, x)) = "" And CDate(verif) < Date And .Cells(y, x - 7) <> "" And _
Trim(.Cells(y, x - 7)) <> "Ref" Then
I = I + 1
ReDim Preserve Tbl(1 To I) 'on redimensionne un tableau qui reprend les travaux non réalisés
Tbl(I) = .Cells(y, x - 8) & "|" & .Cells(y, x - 7) & "|" & .Cells(y, x - 6) & "|" _
& .Cells(y, x - 4) & "|" & .Cells(y, x - 3) & "|" & verif
End If
End If
End With
Next x
Next y
End If
Next Ws
z = 0
For x = 1 To UBound(Tbl) - 1
If Split(Tbl(x), "|")(1) <> Split(Tbl(x + 1), "|")(1) Then
z = z + 1
End If
Next x
ReDim TblSdoub(1 To z + 1, 1 To 6)
z = 0
For x = 1 To UBound(Tbl) - 1
If Split(Tbl(x), "|")(1) <> Split(Tbl(x + 1), "|")(1) Then
z = z + 1
For w = 1 To 6
TblSdoub(z, w) = Split(Tbl(x), "|")(w - 1)
Next w
End If
Next x
For w = 1 To 6
TblSdoub(UBound(TblSdoub, 1), w) = Split(Tbl(UBound(Tbl)), "|")(w - 1)
Next w
With Sheets("A replanifier")
Dl = .Range("C" & .Rows.Count).End(xlUp).Row
If Dl > 2 Then .Range("C3:H" & Dl).ClearContents 'on efface toutes les données
.Range("C3").Resize(UBound(TblSdoub, 1), 6) = TblSdoub 'et on affecte le nouveau résultat
End With
End Sub |
Partager