# Why am i getting a range error for unique random number code

#### JFUCOCHAN

##### New Member
I'm running the following code which is taken from

http://www.mrexcel.com/board2/viewtopic.php?p=1112482&highlight=rand#1112482

Dim MY_RND_NO(80) As Variant
Sub CREATE_RANDOM()
Randomize
Range("C1:C100").ClearContents
MY_COUNT = 1
Do Until MY_COUNT = 101
NEW_NUMBER = Int(Rnd() * (561 - 1) + 1)
If MY_RND_NO(NEW_NUMBER) <> "USED" Then
Range("C" & MY_COUNT).Value = NEW_NUMBER
MY_RND_NO(NEW_NUMBER) = "USED"
MY_COUNT = MY_COUNT + 1
End If
Loop
End Sub

I get a subscript out of range error because i'm trying to run this for 100 cells and over the range of random numbers from 1-561. any thoughts?

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### pgc01

##### MrExcel MVP
Hi Joseph

Try

Dim MY_RND_NO(561) As Variant

HTH
PGC

#### JFUCOCHAN

##### New Member
thank you

works like a charm now.

#### JFUCOCHAN

##### New Member
excel is hanging after 5th loop

I've built a unique random number generator. Now i'm putting this into a loop to excute 100 times. however excel seems to be hanging after the 5th loop. Have not declared enough space for the array?

Sub run()
Dim MY_RND_NO(561) As Variant
Range("t9:t108").Value = 0
Range("c9:c108").ClearContents
Range("b9:b108").ClearContents
For i = 1 To 100
Randomize

MY_COUNT = 1
Do Until MY_COUNT = 101
NEW_NUMBER = Int(Rnd() * (561 - 1) + 1)
If MY_RND_NO(NEW_NUMBER) <> "USED" Then
Range("b" & MY_COUNT + 8).Value = NEW_NUMBER
MY_RND_NO(NEW_NUMBER) = "USED"
MY_COUNT = MY_COUNT + 1
End If
Loop
Range("c9:c108").Value = Range("b9:b108").Value
Calculate
If Range("p4") Then
If Range("t5") < Range("E5") Then
Range("S9:w108").Value = Range("D9:h108").Value
End If
End If
Next
End Sub

#### Norie

##### Well-known Member

Joseph

Which loop do you mean?

Try this.
Code:
``````Sub run()
Dim MY_RND_NO(561) As Variant
Range("t9:t108").Value = 0
Range("c9:c108").ClearContents
Range("b9:b108").ClearContents
For i = 1 To 100
Randomize
MY_COUNT = 1
Do Until MY_COUNT = 101
NEW_NUMBER = Int(Rnd() * (561 - 1) + 1)
If MY_RND_NO(NEW_NUMBER) <> "USED" Then
Range("b" & MY_COUNT + 8).Value = NEW_NUMBER
MY_RND_NO(NEW_NUMBER) = "USED"
MY_COUNT = MY_COUNT + 1
End If
Loop

Range("c9:c108").Value = Range("b9:b108").Value

If Range("p4") Then
If Range("t5") < Range("E5") Then
Range("S9:w108").Value = Range("D9:h108").Value
End If
End If

Erase MY_RND_NO

Next
End Sub``````

#### pgc01

##### MrExcel MVP
Hi again

It should block after the 5th loop.

The array MY_RND_NO is used as auxiliary just to ensure no duplicates.

After your first loop there will be 100 positions in the array marked "USED". This means 2 things

- The 100 random numbers you calculated in the first looped will not be used when you generate the second 100 random numbers group (only 461 numbers available)
- In the second loop you'll marked other 100 positions in the array as used.

This stops after 5 loops. In the sixth loop there are only 61 numbers left.

So, to correct this you have to empty the array after each loop.

You can do this using redim

...
For i=1 to 100
Redim MY_RND_NO(561)
...

HTH
PGC

#### JFUCOCHAN

##### New Member
thank you both. yes i realized that the array was getting filled, but the "erase" command works great!

Replies
1
Views
81
Replies
4
Views
123
Replies
2
Views
227
Replies
3
Views
238
Replies
3
Views
539