Option Compare Text
Dim DerLig As Long, Der_OK_KO As Long, Der_KO As Long, Lig As Long, Qte As Long
Dim Nb_OK_KO As Long
Dim Stock As String, Piece As String, Col_Qte As String, Col As String
Sub Repartition()
Application.ScreenUpdating = False
DerLig = Range("A" & Rows.Count).End(xlUp).Row
Randomize
For o = 1 To 2
Select Case o
Case 1
Stock = "OK"
For s = 1 To 4
Select Case s
Case 1
Piece = "chambre"
Col = "I"
Col_Qte = "M"
Case 2
Piece = "cuisine"
Col = "H"
Col_Qte = "L"
Case 3
Piece = "toilette"
Col = "G"
Col_Qte = "K"
Case 4
Piece = "salle de bain"
Col = "F"
Col_Qte = "J"
End Select
Traitement
Next s
Case 2
Stock = "KO"
For s = 1 To 4
Select Case s
Case 1
Piece = "chambre"
Col = "M"
Col_Qte = "M"
Case 2
Piece = "cuisine"
Col = "L"
Col_Qte = "L"
Case 3
Piece = "toilette"
Col = "K"
Col_Qte = "K"
Case 4
Piece = "salle de bain"
Col = "J"
Col_Qte = "J"
End Select
Traitement
Next s
End Select
Next o
Range("E11:E1000,S1:U1000").ClearContents
End Sub
Sub Traitement()
Range("E11:E1000").ClearContents
Range("S1:U1000").ClearContents
Range("A1:C1").AutoFilter
ActiveSheet.Range("A1:C" & DerLig).AutoFilter Field:=1, Criteria1:=Piece
ActiveSheet.Range("A1:C" & DerLig).AutoFilter Field:=3, Criteria1:=Stock
Der_OK_KO = Range("_FilterDataBase").Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
Range("_FilterDataBase").Resize(, 3).SpecialCells(xlCellTypeVisible).Copy
Range("S1").Select
ActiveSheet.Paste
ActiveSheet.ShowAllData
Qte = Cells(3, Col_Qte)
Lig = 11
For i = 2 To Qte + 1
Nb_OK_KO = Int((Der_OK_KO * Rnd) + 2)
If Nb_OK_KO > Der_OK_KO Then Nb_OK_KO = Der_OK_KO
If Application.WorksheetFunction.CountIf(Range("E11:E" & Lig - 1), Nb_OK_KO) = 0 Then
Cells(Lig, "E") = Nb_OK_KO
Cells(Lig, Col) = Cells(Nb_OK_KO, "T")
Lig = Lig + 1
Else
i = i - 1
End If
Next i
End Sub