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

JFUCOCHAN

New Member
Joined
Jun 12, 2003
Messages
33
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?
 

Some videos you may like

Excel Facts

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

JFUCOCHAN

New Member
Joined
Jun 12, 2003
Messages
33
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
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 25, 2006
Messages
19,870
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
Joined
Jun 12, 2003
Messages
33
thank you both. yes i realized that the array was getting filled, but the "erase" command works great!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,856
Messages
5,544,690
Members
410,629
Latest member
foozen
Top