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.Description)

End Sub

"Laphan" <ne**@DoNotEmailMe.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