Bonjour,
va dans les macros (Alt+F11), clix droit sur Feuille AideAffectation
> Code, et change ceci : Sheets("Sites")
Private Sub UserForm_Activate()
Dim Dispo As Range, SurSite As Range, CelV As Range, CelH As Range
Dim i%, qui$, ici$, equipe%, JourDebut As Long, JourFin As Long, numJour%
Me.emploi.Caption = Cells(ligne, 1) & " - du " & Cells(1, colonne) & IIf(nbJours > 1, " au " & Cells(1, colonne + nbJours - 1), "")
Me.equipier.AddItem ("") ' pour pouvoir effacer
For i = 2 To Sheets("Competences").[A65000].End(xlUp).Row
qui = Sheets("Competences").Cells(i, 1)
equipe = Sheets("Competences").Cells(i, 2)
JourDebut = Cells(1, colonne)
ici = Split(Cells(ligne, 1), " ")(1)
JourFin = Cells(1, colonne + nbJours - 1)
numJour = Sheets("Calendrier").[A2].Offset(JourDebut - Sheets("Calendrier").[A2], equipe + 2)
Set CelV = Range(Cells(1, colonne), Cells([A65000].End(xlUp).Row, colonne + nbJours - 1))
Set CelH = Range(Cells(ligne, Application.max(2, colonne - numJour + 1)), Cells(ligne, colonne - numJour + 6))
Set Dispo = Range(Sheets("Indisponibilites").Cells(i, JourDebut - Sheets("Indisponibilites").[B1] + 2), Sheets("Indisponibilites").Cells(i, JourFin - Sheets("Indisponibilites").[B1] + 2))
Set SurSite = Range(Sheets("Sites").Cells(i, JourDebut - Sheets("Sites").[B1] + 2), Sheets("Sites").Cells(i, JourFin - Sheets("Sites").[B1] + 2))
If (WorksheetFunction.CountIf(CelV, qui) - WorksheetFunction.CountIf(Selection, qui)) = 0 _
And WorksheetFunction.CountBlank(Dispo) = nbJours _
And WorksheetFunction.CountIf(SurSite, "<>" & ici) = 0 _
And Not Repos(equipe) _
And WorksheetFunction.CountIf(CelH, qui) + nbJours <= Len(Sheets("Competences").Cells(i, ligne - 2)) _
Then Me.equipier.AddItem (qui)
Next i
If Me.equipier.ListCount = 1 Then
Me.equipier.Clear
Me.Hide
MsgBox "Pas de possibilité !"
End If
End Sub