Macro pour tirage au sort conditionnel

[chich]

Bonjour
en cherchent j'ai trouve
une macro Tirage au sort qui fais en partie ce que je souhaite pour mon projet
j'ai une liste de chiffre chacun correspond a un collaborateur
Dans les cellule BA,BC,BE,BG,BI,BK,BN
j'aimerai faire dans un ligne sur trois les quel
il y a une date tout les jours de l’année un tirage au sort aléatoire 7 chiffre dans la liste des collaborateurs qui n'on pas de motif d'absence
CP,CN,CD,MA,MOD ,CSS et le chiffre qui se trouve dans la plage ( H7:AX7)
je vous joint fichier échantillon
Merci d'avance

Sub Tirage_au_sort()
Dim i As Integer, DerLig As Integer

Application.ScreenUpdating = False

With Sheets("Feuil2")
DerLig = .Range("A1048576").End(xlUp).Row
.Range("A14:A23" & DerLig).Copy Destination:=.Range("BR14")
For i = 14 To DerLig
.Range("BQ" & i) = Rnd
Next
.Range("BQ14:BR" & DerLig).Sort Key1:=.Range("BQ14"), Order1:=xlAscending, Header:=xlNo

Range("BA4") = .Range("BR14")
Range("BC4") = .Range("BR15")
Range("BE4") = .Range("BR16")
Range("BG4") = .Range("BR17")
Range("BI4") = .Range("BR18")
Range("BK4") = .Range("BR19")
Range("BM4") = .Range("BR20")
.Range("BQ14:BR1048576").ClearContents
End With

End Sub