Random

[JB_Belgium]

Annex file is an example...

Each participant have a unique number (1000 => 1060).
The participant receive for each serie a number of a ticket.

BUT.
The participant may never get the same batch number assigned.

I search a macro that give the participants a ticket for the serie (random assigned).


Thanks
8317

[snb]

Sub snb()
Randomize
ReDim sn(59, 1)

For j = 0 To UBound(sn)
sn(j, 0) = Rnd
Next

Range("A1:A60") = sn
Range("A1:A60") = [index(1000+rank(A1:A60,A1:A60),)]
End Sub

[JB_Belgium]

Sub snb()
Randomize
ReDim sn(59, 1)

For j = 0 To UBound(sn)
sn(j, 0) = Rnd
Next

Range("A1:A60") = sn
Range("A1:A60") = [index(1000+rank(A1:A60,A1:A60),)]
End Sub

Maybe misinterpreted...

What I asked was a new assigned ticket in sheet 3 (col D, Q, AD, Q)
Available tickets in column A (sheet 3)
The tickets (col A) can be different in each sheet

[CodeNinja]

Not quite sure if this is what you are looking for, but hopefully, it will be a good start to you...

Sub test()
Dim icol As Integer
Dim iUsedNumbers(1 To 20) As Integer
Dim irow As Integer
Dim i As Integer
Dim iTemp As Integer
Dim bInArray As Boolean


Randomize 'need this to ensure numbers are really random.

For icol = 0 To 3
For irow = 1 To 20

' check to see if the rnd number has already been used.
Do
bInArray = False
iTemp = (Rnd() * 20) + 1 'get random number 1 - 20
For i = 1 To 20
If iUsedNumbers(i) = iTemp Then bInArray = True
Next i
Loop Until bInArray = False
' put number in cell
Sheets("Serie 3").Cells(irow + 3, 4 + (icol * 13)) = iTemp
' add number to used numbers array
For i = 1 To 20
If iUsedNumbers(i) = 0 Then
iUsedNumbers(i) = iTemp
i = 20
End If
Next i
Next irow
'Clear usedNumbers Array
For i = 1 To 20
iUsedNumbers(i) = 0
Next i
Next icol

End Sub

[Aussiebear]

What I asked was a new assigned ticket in sheet 3 (col D, Q, AD, Q)
Available tickets in column A (sheet 3)
The tickets (col A) can be different in each sheet

That is not what you asked for initially. Your initial post whilst it included an annexed file ( and you need to remember here that some people never open files from others), did not include that you wanted a new assigned ticket in Sheet 3.