Try using a Unique Random Number Generator. This will not repeat the same
number.
Generating Random numbers can be very useful at time, however, a randomly
generated number from a seed number can generate or repeat the same number
if a full list or array on numbers is required. This procedure will generate
a Unique Random Number. This means the generated number will not be
repeated. If the seed for the Random Number were 250, then 250 numbers will
be randomly generated without repeating any of the random generated numbers.
Here is one you might use.
Public Sub GenerateNumber( )
On Error GoTo ErrorHandler
Dim AC As Integer
Dim I As Integer
Dim R As Integer
Dim Response
'Define the flagged array
Dim ACount(250)
'Define the string variable
Dim ANum(250)
'Define the RANDOM number seed
AC = 25
'Set the flags to FALSE
For I = 1 To AC
ACount(I) = False
Next I
'Iteration
For I = 1 To AC
Jump1:
'Exit the generator
If I > AC Then Exit Sub
'Generate RANDOM number from a base defined by AC
R = Int((AC * Rnd) + 1)
'Test the flag for TRUE
If ACount(R) = True Then
GoTo Jump1
End If
'Test the flag for FALSE
If ACount(R) = False Then
'Set the flag to TRUE
ACount(R) = True
'Assign the RANDOM number to an array
ANum(I) = R
'Do what you want with the generated number here.
'You can call another routine if desired
DisplayNumber
End If
Next I
Exit Sub
ErrorHandler:
Response = MsgBox(Err.Desc ription)
End Sub
"Laphan" <ne**@DoNotEmai lMe.co.uk> wrote in message
news:3f******** @127.0.0.1...
Hi All
This is a strange request, but I just cannot fathom how to do it.
In theory the requirement is very basic, but in practise its a noodle!!
I have 10 team names like so:
Team A
Team B
Team C
etc ..
I want to create a sort of fixture list that makes sure that each team
'plays' each other just once at home (ie, on the left side) and once away
(ie, on the right side), eg:
Team A v Team B
Team C v Team D
etc..
for what will be 9 weeks (??)
I then want to put this into an Access DB, which is easy enough, but the
former problem is a lot harder.
My initial idea was to enter all the permutations and then use a random
number generator to pick 10 matches for each week's fixture, but this was
a crap idea because I can get the same team's matches listed 4 or 5 times in
the same week.
Any ideas?
Rgds
Laphan