Random numbers not using randbetween()

Hi everyone,
I have been trying repeatedly for days now to write a macro to generate random numbers. I need to generate 23 unique numbers in 5 columns. I have found a macro that can do one column at a time but i cannot modify this to work over the five columns.

Here is the code for you to look at:

Sub CreateRand()
Dim Nums(23)
Dim I As Integer
Dim X As Integer
Dim rng As Range
Dim c As Range
Dim Filled As Boolean

For I = 1 To 23
Nums(I) = I
Next I

Set rng = Range("f1:f23")

For Each c In rng

Do

X = Int((Rnd * 23) + 1)
If Nums(X) <> 0 Then
c.Value = Nums(X)
Nums(X) = 0
Filled = True
End If
Loop Until Filled
Filled = False
Next c
End Sub
All help will be greatly received!!!!!!


Thanks, a slowly going bald Excel user

Hi again,

Yes the numbers need to be between 1 and 23 in all five columns.

What i am doing in more depth is:

we have 23 groups and 23 activities. All of the 23 groups need to carry out activities at random over 5 periods. So thats a total of 5 activities each. What i have got is a grid that generates the random numbers, i then do a lookup on the numbers to get the activity I.E. Swimming.

The source worksheet looks something like:


P1 P2 P3 P4 P5
Activity 1 15 18 5 22 21
Activity 2 13 7 16 12 6
Activity 3 14 16 18 8 22
Activity 4 7 6 15 10 20
Activity 5 18 3 4 6 11
Activity 6 1 22 19 5 12
Activity 7 19 8 23 1 18
Activity 8 2 19 8 18 9
Activity 9 10 12 9 7 5
Activity 10 20 2 11 17 8
Activity 11 9 5 3 20 10
Activity 12 23 20 17 19 4
Activity 13 21 14 13 11 15
Activity 14 22 1 10 2 3
Activity 15 11 13 21 4 16
Activity 16 17 15 1 14 23
Activity 17 6 10 7 3 1
Activity 18 16 23 22 13 7
Activity 19 3 4 12 15 19
Activity 20 4 17 6 21 14
Activity 21 8 21 2 9 17
Activity 22 5 9 20 23 13
Activity 23 12 11 14 16 2


I am using office 2003.

Hope that this helps. Dave.

Hi Everyone,

This has now been sorted thnks to a nice chap from elsewhere. I fanybody would like the answer say so and i will post it.

You need 23 numbers spread over 5 columns?

You need five columns each with 23 unique numbers ( 5 x 23 unique numbers
total)?
You need five columns each with 23 unique numbers within the individual
column (23 unique numbers total repeated 5 times)?

--
Regards,
Tom Ogilvy



"davehill1974" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi everyone,
> I have been trying repeatedly for days now to write a macro to generate
> random numbers. I need to generate 23 unique numbers in 5 columns. I
> have found a macro that can do one column at a time but i cannot modify
> this to work over the five columns.
>
> Here is the code for you to look at:
>
> Sub CreateRand()
> Dim Nums(23)
> Dim I As Integer
> Dim X As Integer
> Dim rng As Range
> Dim c As Range
> Dim Filled As Boolean
>
> For I = 1 To 23
> Nums(I) = I
> Next I
>
> Set rng = Range("f1:f23")
>
> For Each c In rng
>
> Do
>
> X = Int((Rnd * 23) + 1)
> If Nums(X) <> 0 Then
> c.Value = Nums(X)
> Nums(X) = 0
> Filled = True
> End If
> Loop Until Filled
> Filled = False
> Next c
> End Sub
> All help will be greatly received!!!!!!
>
>
> Thanks, a slowly going bald Excel user
>
>
> --
> davehill1974
> ------------------------------------------------------------------------
> davehill1974's Profile:
http://www.excelforum.com/member.php...o&userid=24995
> View this thread: http://www.excelforum.com/showthread...hreadid=385218
>

Change F1:F23 to the first set of 23 cells (next to the activities titles)
Sub CreateRand()
Dim Nums(23)
Dim I As Integer
Dim X As Integer
Dim rng As Range
Dim c As Range
Dim Filled As Boolean

For I = 1 To 23
Nums(I) = I
Next I

Set rng = Range("f1:f23")
for i = 0 to 4
For Each c In rng.offset(0,i)

Do

X = Int((Rnd * 23) + 1)
If Nums(X) <> 0 Then
c.Value = Nums(X)
Nums(X) = 0
Filled = True
End If
Loop Until Filled
Filled = False
Next c
Next i
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>
> You need 23 numbers spread over 5 columns?
>
> You need five columns each with 23 unique numbers ( 5 x 23 unique numbers
> total)?
> You need five columns each with 23 unique numbers within the individual
> column (23 unique numbers total repeated 5 times)?
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "davehill1974" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Hi everyone,
> > I have been trying repeatedly for days now to write a macro to generate
> > random numbers. I need to generate 23 unique numbers in 5 columns. I
> > have found a macro that can do one column at a time but i cannot modify
> > this to work over the five columns.
> >
> > Here is the code for you to look at:
> >
> > Sub CreateRand()
> > Dim Nums(23)
> > Dim I As Integer
> > Dim X As Integer
> > Dim rng As Range
> > Dim c As Range
> > Dim Filled As Boolean
> >
> > For I = 1 To 23
> > Nums(I) = I
> > Next I
> >
> > Set rng = Range("f1:f23")
> >
> > For Each c In rng
> >
> > Do
> >
> > X = Int((Rnd * 23) + 1)
> > If Nums(X) <> 0 Then
> > c.Value = Nums(X)
> > Nums(X) = 0
> > Filled = True
> > End If
> > Loop Until Filled
> > Filled = False
> > Next c
> > End Sub
> > All help will be greatly received!!!!!!
> >
> >
> > Thanks, a slowly going bald Excel user
> >
> >
> > --
> > davehill1974
> > ------------------------------------------------------------------------
> > davehill1974's Profile:
> http://www.excelforum.com/member.php...o&userid=24995
> > View this thread:
http://www.excelforum.com/showthread...hreadid=385218
> >
>
>

Hello,

I presume that no group should attend the same activity twice.

So:

Option Explicit

Public Sub gen5periods()

Dim i As Long
Dim j As Long
Dim k As Long
Dim brepeat As Boolean

Range("B2:B24").FormulaArray = "=uniqrandint(23,false)"
For i = 3 To 6
brepeat = True
Do While brepeat
Range(Cells(2, i), Cells(24, i)).FormulaArray =
"=uniqrandint(23,false)"
brepeat = False
For j = 2 To i - 1
For k = 2 To 24
If Cells(k, j).Value = Cells(k, i).Value Then
brepeat = True
End If
Next k
Next j
Loop
Next i

End Sub

My function UniqRandInt() you can find at:
http://www.sulprobil.com/html/uniqrandint.html

HTH,
Bernd

Hi,

I have tried this and it made Excel freeze! I left it running for an hour just to make sure.

Thanks for the reply though.
You never know i may need to just leave Excel running fr a further hour!!!!