# Unique random numbers in their proper places

#### Mike7

##### Board Regular
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
ABCDEFGHIJKLMNOPQRSTU
11234567891011121314151617181920
219111415
32256
43712
54420
651018
76181619
87317
98
10913
Sheet1

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi, Mike,

this fills all blocks for me
for you too ?
Code:
``````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

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 ?

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.

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

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.

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

Replies
2
Views
297
Replies
11
Views
193
Replies
6
Views
329
Replies
3
Views
137
Replies
2
Views
328

1,196,277
Messages
6,014,406
Members
441,818
Latest member
itsfaisalkhalid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back