Random numbers not using randbetween()

[davehill1974]

Hi everyone,
I have been trying repeatedly for days now to write a macro to generat
random numbers. I need to generate 23 unique numbers in 5 columns.
have found a macro that can do one column at a time but i cannot modif
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 use

 

[swatsp0p]

untested, but what if you set your range to Set rng = Range("f1:j23")
instead of Set rng = Range("f1:f23")?

Good Luck

 

[davehill1974]

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!!!!

 

[swatsp0p]

do the random numbers need to be between 1 and 23 in each of the five
columns?

 

[davehill1974]

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.

 

[davehill1974]

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.

 

[Tom Ogilvy]

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]...

 

[Tom Ogilvy]

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

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

 

[Guest]

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