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

[JFUCOCHAN]

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?

 

[pgc01]

Hi Joseph

Try

Dim MY_RND_NO(561) As Variant

HTH
PGC

 

[JFUCOCHAN]

thank you

works like a charm now.

 

[JFUCOCHAN]

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]

Joseph

Which loop do you mean?

Try this.

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]

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]

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