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
| Option Explicit
Sub Remplacement(r As Range)
With r.Validation
.Delete
.Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=ListeDispo(r)
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
Private Function ListeDispo(r As Range) As String
'--- suppose tableau parfaitement bien ordonné
'--- équipes en lignes: 6 - 22 - 38 - 54 - 70 - 86 - 102
Dim kR As Long, kC As Long, Eqp As Long, s As String, kREq As Long
s = ""
kR = r.Row
kC = r.Column
For Eqp = 1 To 7
kREq = 6 + (Eqp - 1) * 16 + 1 '--- n° ligne Planning de l'équipe
If Cells(kREq, kC) = "REPOS" Or Cells(kREq, kC) = "J" Then '--- libre ce jour
If Cells(kREq, kC + 1) = "REPOS" Or Cells(kREq, kC + 1) = "J" Then '--- libre le jour suivant
'--- membres de cette équipe admis à effectuer un remplacement
For kR = 1 To 11 Step 2
If Cells(kREq + kR, kC) <> "ABS" And Cells(kREq + kR, 1) <> 0 Then
s = s & "," & Cells(kREq + kR, 1)
End If
Next kR
End If
End If
Next Eqp
If s = "" Then
ListeDispo = ""
Else
ListeDispo = Mid(s, 2)
End If
End Function |
Partager