Sub Tirages()
Dim nom, nnom%, h%, corvee As Range, suppleant As Range
Dim ecart As Range, i%, r1%, r2%
nom = [C2:O2] 'à adapter
nnom = UBound(nom, 2)
h = Day(Application.EoMonth([B9], 0))
Set corvee = [C9].Resize(h)
Set suppleant = [D9].Resize(h)
Set ecart = [J43]
Application.ScreenUpdating = False
Randomize
corvee.ClearContents 'RAZ
suppleant.ClearContents 'RAZ
For i = 1 To h
Do
r1 = 1 + Int(Rnd * nnom)
corvee(i) = nom(1, r1)
Loop While ecart > 2 Or corvee(i) = corvee(i - 1) Or corvee(i) = suppleant(i - 1)
Do
r2 = 1 + Int(Rnd * nnom)
suppleant(i) = nom(1, r2)
Loop While r1 = r2 Or ecart > 2 Or suppleant(i) = corvee(i - 1)
Next i
End Sub