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
| Sub Check()
Dim DateCell As Range
Dim CodeCell As Range
Dim ErrorCell As Range
Dim CodesCounter As Long
Dim Result As Long
Dim Offset As Long
Dim DataRange As Range
Dim ErrorRange As Range
Set DateCell = shPlanning.Range("b2")
Set ErrorRange = Range("Erreurs_Jour")
Set ErrorCell = ErrorRange(1)
Set DataRange = Range("Missions_Jour")
Do While DateCell.Value <> ""
ErrorRange.ClearContents
For CodesCounter = 1 To Range("t_Codes[code*]").Cells.Count
Offset = WeekDay(DateCell.Value, vbMonday) + 2
If Range("t_codes[code*]")(CodesCounter, Offset).Value = "X" Then
Result = Application.WorksheetFunction.CountIfs(DataRange, Range("t_codes[code*]")(CodesCounter).Value)
If Result = 0 Then
ErrorCell.Value = Range("t_codes[code*]")(CodesCounter)
Set ErrorCell = ErrorCell(2)
End If
End If
CodesCounter = CodesCounter + 1
Next
Set DateCell = DateCell(1, 3)
Set DataRange = DataRange.Offset(0, 2)
Set ErrorRange = ErrorRange.Offset(0, 2)
Set ErrorCell = ErrorRange(1)
Loop
End Sub |
Partager