Unique random numbers in their proper places

[Mike7]

Question for the masters - is it possible to do such trick with random numbers in Excel?
I need to generate 40 random numbers from 1 to 40 placed in 9 rows. 5 rows with 4 numbers and 4 rows with 5 numbers, does not matter which rows. Numbers must be in their proper places by order of B1:AO1 .
Below is smaller example of wanted result of one block. Actually I need 96 blocks in one sheet - range B2:AO865

Old5.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2	1									9		11			14	15
3	2		2			5	6
4	3							7					12
5	4				4																20
6	5										10								18
7	6	1							8								16			19
8	7			3														17
9	8
10	9													13
Sheet1

 

[erik.van.geit]

Hi, Mike,

this fills all blocks for me
for you too ?

Option Explicit
Option Base 1

Sub random_stuff()
'Erik Van Geit
'051108
'regards to NateO for the random-sort-idea
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant, arr4 As Variant
Dim cnt As Long
Dim i As Long, f As Long, k As Integer, j As Integer, l As Integer, counter As Integer
Const loops As Integer = 96

Application.ScreenUpdating = False

Range("B2:AO865") = ""

Randomize Timer

For counter = 1 To loops

'choose 4 rows of 5
arr1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
ReDim arr2(1 To 4)
cnt = UBound(arr1, 1)
    For i = 1 To 4
        f = Int((cnt - LBound(arr1, 1) + 1) * _
            Rnd + LBound(arr1, 1))
        arr2(i) = arr1(f)
        arr1(f) = arr1(cnt)
        arr1(cnt) = arr2(i)
        cnt = cnt - 1
    Next
    
'generate array with 40 # out of 1 to 9 - sorted
arr1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
ReDim arr3(1 To 40)
l = 0
    For i = 1 To 9
        If IsError(Application.Match(i, arr2, 0)) Then j = 4 Else j = 5
        For k = 1 To j
        l = l + 1
        arr3(l) = arr1(i)
        Next k
    Next i
    
'randomize order of precedent array
ReDim arr4(1 To 40)
cnt = UBound(arr3, 1)
    For i = 1 To 40
        f = Int((cnt - LBound(arr3, 1) + 1) * _
            Rnd + LBound(arr3, 1))
        arr4(i) = arr3(f)
        arr3(f) = arr3(cnt)
        arr3(cnt) = arr4(i)
        cnt = cnt - 1
    Next

'put on sheet
    For i = 1 To 40
    Cells(arr4(i) + (counter - 1) * 9 + 1, i + 1) = i
    Next i
    
Next counter

Application.ScreenUpdating = True
End Sub

could still be enhanced (for instance: put 40 and 9 in a variable)

kind regards,
Erik

 

[Mike7]

Thanx first.
And sorry, but I'm completely with VBA.
Could anyone please explain me step by step how to get it working. Where to paste it, how to start it etc ?

 

[Mike7]

Ok, I tried by myself.
This way - Tools - Macro - VBA Editor, then paste.
Next - Tools - Macro - Macros - then press on this function and press Run.
It works Great!
Thanx again, I need 36 sheets like this, so imagine what a big piece of time you have saved for me.

 

[erik.van.geit]

OK, Mike, you're welcome.
Very curious: what is the purpose of this ? Can you explain a bit ?

 

[Mike7]

I need it for my lottery system.
Later I will also need to generate 100 random numbers from 0 to 99 placed in 3 rows. 200 blocks, range B2:CW601. It's for Pick3 lottery.
Will try to modify your code. But if you are not so busy could you please do it for me.
Thanx again.

 

[erik.van.geit]

my lottery system

do you mean you want to find a trick to have more chance to win lottery ?

recently I replied this
I hope you are aware of the fact that any combination has as much chance to come out as the others, also as any combination which came out in the past

if you find it "ridiculous" to play this combination
1 2 3 4 5 6
then don't play lotto !

if you think you will get more chance by trying to find a system
then don't play lotto !!

if you think 7 12 24 33 38 41 has more chance to win then 7 9 11 13 15 17
then don't play lotto !!!

just play around with the code
if you can't change it to your suits I'll be ready to help

best regards,
Erik