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
| Dim Nom As String, i As Byte, verif As Boolean
recom:
verif = False
Nom = InputBox("Service Call number of your intervention") ', "Create a Gantt Chart")
If Application.CountIf(Range("R3:R20000"), Nom) <= 1 Then
If verif = False Then
MsgBox "Service Call doesn't exist!"
GoTo recom
End If
End If
If Nom = "" Then Exit Sub
For i = 1 To Sheets.count
If Sheets(i).Name = Nom Then verif = True
Next
If verif = True Then
MsgBox "Gantt Chart for SC " & Nom & " already created!"
GoTo recom
End If
Sheets.Add(after:=Sheets(Sheets.count)).Name = Nom
' creer le tableau
Dim plage As Range
Dim Cel As Range
Dim Tbl() As String
Dim X As Integer
With Worksheets("Planning"): Set plage = .Range(.Cells(4, 18), .Cells(.Rows.count, 18).End(xlUp)): End With
For Each Cel In plage
If Cel.Value = Nom Then
X = X + 1: ReDim Preserve Tbl(1 To 8, 1 To X)
Tbl(1, X) = Cel.Offset(, -11).Value
Tbl(2, X) = Cel.Offset(, -10).Value
Tbl(3, X) = Cel.Offset(, -7).Value
Tbl(4, X) = Cel.Offset(, -5).Value
Tbl(5, X) = Cel.Offset(, -4).Value
Tbl(6, X) = Cel.Offset(, -3).Value
Tbl(7, X) = Cel.Offset(, -2).Value
Tbl(8, X) = Cel.Offset(, -1).Value
End If
Next Cel
With Worksheets(Nom): .Range(.Cells(8, 4), .Cells(UBound(Tbl, 2) + 7, UBound(Tbl, 1) + 3)).Value = Application.Transpose(Tbl()):
End With |