Option Explicit
Sub Test()
Dim i As Byte, y() As Variant, z() As Variant, x As Integer, c As New Collection
Randomize
y = Array(16, 17, 18)
z = Array(9, 25, 42)
For i = 0 To 2
Do While c.Count < 3
x = Int(y(i) * Rnd + z(i))
If Cells(x, 3) = 1 Then
On Error Resume Next
If Cells(x, 3).Interior.ColorIndex <> 3 Then _
c.Add Cells(x, 3).Address, CStr(Cells(x, 3).Address)
If Err = 0 Then
With Sheets("Mois en cours").Range("F4")
.Value = .Value & Cells(x, 3).Address(0, 0) & vbLf
End With
End If
On Error GoTo 0
End If
Loop
Set c = Nothing
Next i
End Sub
With Sheets("Mois en cours").Range("F4")
Option Explicit
Sub Test()
Dim i As Byte, y() As Variant, z() As Variant, x As Integer, c As New Collection
Dim p As Range
Randomize
y = Array(16, 17, 18)
z = Array(9, 25, 42)
For i = 0 To 2
Do While c.Count < 3
x = Int(y(i) * Rnd + z(i))
If Cells(x, 3) = 1 Then
On Error Resume Next
If Cells(x, 3).Interior.ColorIndex <> 3 Then _
c.Add Cells(x, 3).Address, CStr(Cells(x, 3).Address)
If Err = 0 Then
For Each p In Sheets("Mois en cours").Range("F4:F34")
If p.Interior.ColorIndex <> 6 And IsEmpty(p.Value) Then
p.Value = Cells(x, 3).Address(0, 0)
Exit For
End If
Next p
End If
On Error GoTo 0
End If
Loop
Set c = Nothing
Next i
End Sub
p.Value = Cells(x, 3).Address(0, 0)
p.Value = Cells(x, [COLOR="Red"][B]1[/B][/COLOR]).[COLOR="red"][B]Values[/B][/COLOR]
Option Explicit
Sub Test()
Dim i As Byte, y() As Variant, z() As Variant, x As Integer, c As New Collection
Dim p As Range
Randomize
y = Array(16, 17, 18)
z = Array(9, 25, 42)
For i = 0 To 2
Do While c.Count < 3
x = Int(y(i) * Rnd + z(i))
If Cells(x, 3) = 1 Then
On Error Resume Next
If Cells(x, 3).Interior.ColorIndex <> 3 Then _
c.Add Cells(x, 3).Address, CStr(Cells(x, 3).Address)
If Err = 0 Then
For Each p In Sheets("Mois en cours").Range("F4:F34")
If p.Interior.ColorIndex <> 6 And IsEmpty(p.Value) Then
p.Value = Cells(x, 1)
Exit For
End If
Next p
End If
On Error GoTo 0
End If
Loop
Set c = Nothing
Next i
End Sub
Option Explicit
Sub test()
Dim i As Byte, y() As Variant, z() As Variant, x As Integer, c As New Collection
Dim p As Range
Randomize
y = Array(16, 17, 18)
z = Array(9, 25, 42)
For i = 0 To 2
Do While c.Count < 3
x = Int(y(i) * Rnd + z(i))
If Cells(x, 3) = 1 And Cells(x, 3).Interior.ColorIndex <> 3 Then
On Error Resume Next
c.Add Cells(x, 3).Address, CStr(Cells(x, 3).Address)
If Err = 0 Then
On Error GoTo 0
For Each p In Sheets("Mois en cours").Range("F4:F34")
If p.Interior.ColorIndex <> 6 And IsEmpty(p.Value) Then
p.Value = Cells(x, 1).Value
Exit For
End If
Next p
End If
On Error GoTo 0
End If
Loop
Set c = Nothing
Next i
End Sub