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 51 52 53 54
| Public varcol As Integer
Public Varfeuille As String, Lg As Integer, Tbl(), TblSdoub()
Public Dl As Integer, x As Integer, y As Integer
Sub replan()
Dim i As Long, z As Integer, w As Integer
Dim Ws As Worksheet
i = 0
Dl = 113
For y = 6 To Dl
z = 2
For Each Ws In Worksheets
If Ws.Name Like "Prévi*" Then
For x = 10 To 49 Step 9
With Ws
If UCase(.Cells(y, x)) = "" And .Cells(y, x - 6) <> "" And CDate(.Cells(z, x - 7)) < Date And _
Trim(.Cells(y, x - 6)) <> "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 - 7) & "|" & .Cells(y, x - 6) & "|" & .Cells(y, x - 5) & "|" & .Cells(y, x - 4) _
& "|" & .Cells(y, x - 3) & "|" & .Cells(y, x - 2)
z = z + 28
End If
End With
Next x
End If
Next Ws
Next y
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("A" & .Rows.Count).End(xlUp).Row
If Dl > 1 Then .Range("A2:F" & Dl).ClearContents
.Range("A2").Resize(UBound(TblSdoub, 1), 6) = TblSdoub
End With
End Sub |
Partager