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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
| Option Explicit
Sub reportingPlanning()
Dim ligne As Long
Dim equipe As String
Dim Numroulement As Byte
Dim stage As String
Dim DateStage As Range
Dim cellule As Range
Dim CelluleResultat As Range
Application.ScreenUpdating = False
Set DateStage = Range(Range("E2"), Range("E2").End(xlToRight))
For Each cellule In DateStage
Sheets("SYNTHESE").Select
Columns("A:A").Select
On Error GoTo PasDeDate
Set CelluleResultat = Selection.Find(What:=CDate(cellule.Value), After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Sheets("PLANNING").Select
For ligne = 4 To Range("Tplanning").Rows.Count
equipe = Cells(ligne, 2)
Numroulement = CByte(Right(Cells(ligne, 3), 1))
stage = Cells(ligne, 4)
If Not IsError(CelluleResultat.Offset(0, Numroulement)) Then
If InStr(1, CelluleResultat.Offset(0, Numroulement), equipe) > 0 Then
Cells(ligne, cellule.Column) = "X"
End If
End If
Next
PasDeDate:
Next
Application.ScreenUpdating = True
MsgBox "Reporting terminé"
End Sub
Sub ReportingCritere()
Dim plageFormation As Range
Dim plageStage As Range
Dim cellule As Range
Dim mini As Integer
Dim maxi As Integer
Dim i As Integer
Dim j As Long
Application.ScreenUpdating = False
j = 3
Sheets("CRITERE").Select
Set plageFormation = Range(Range("A2"), Range("A2").End(xlDown))
For Each cellule In plageFormation
mini = cellule.Offset(0, 1)
maxi = cellule.Offset(0, 2)
Sheets("PLANNING").Select
ActiveSheet.ListObjects("Tplanning").Range.AutoFilter Field:=4, Criteria1:=cellule.Value
Set plageStage = Range(Range("D3"), Range("D3").End(xlDown)).SpecialCells(xlCellTypeVisible)
For i = 1 To Range("Tplanning").Columns.Count - 4
If Application.WorksheetFunction.CountA(plageStage.Offset(0, i)) >= mini And _
Application.WorksheetFunction.CountA(plageStage.Offset(0, i)) <= maxi Then
cellule.Offset(0, j).Value = Cells(2, plageStage.Offset(0, i).Column)
j = j + 1
End If
Next
j = 3
Next
ActiveSheet.ShowAllData
Application.ScreenUpdating = True
MsgBox "Traitement terminé"
End Sub |
Partager