Randomize individual numbers

barissh

Board Regular
Joined
Aug 10, 2006
Messages
94
I have a macro which is creating randomize different numbers. But this code generates only 22 numbers and then freezing. I don't understand why.

Can someone help me how to create (not equal) 30 numbers or more ?


Code:
Sub test()
Dim num As Long, x As Range, a As Long
Randomize
For a = 1 To 29
    Do
        num = Int((30 * Rnd) + 1)
        Set x = Range("A1:A30").Find(num)
    Loop Until x Is Nothing
    Range("A" & a) = num
Next a
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings,

You can try this if you'd like. In a new/blank wb...

In a Standard Module:<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Function</SPAN> MULTIRAND(<SPAN style="color:#00007F">Optional</SPAN> LowNumber<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN> = 1, _<br>                  <SPAN style="color:#00007F">Optional</SPAN> HighNumber<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>                  <SPAN style="color:#00007F">Optional</SPAN> HowManyVals<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>                  <SPAN style="color:#00007F">Optional</SPAN> NonVol<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN>, _<br>                  <SPAN style="color:#00007F">Optional</SPAN> Dim1_Ubound<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>                  <SPAN style="color:#00007F">Optional</SPAN> Dim2_UBound<SPAN style="color:#00007F">As</SPAN> Long _<br>                   )<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br>Dim _<br>Target          <SPAN style="color:#00007F">As</SPAN> Range, _<br>i              <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>x              <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>y              <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>aryVals        <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>, _<br>aryTemp        <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br><br><SPAN style="color:#00007F">Const</SPAN> ULIMIT    <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN> = 15000<br><br>    <SPAN style="color:#00007F">If</SPAN> TypeName(Application.Caller) = "Range"<SPAN style="color:#00007F">Then</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> NonVol<SPAN style="color:#00007F">Then</SPAN> Application.Volatile<br>        <SPAN style="color:#00007F">Set</SPAN> Target = Application.Caller<br>        <br>        <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> HowManyVals = 0<SPAN style="color:#00007F">Then</SPAN> HowManyVals = Target.Cells.Count<br>            <SPAN style="color:#00007F">If</SPAN> HighNumber = 0<SPAN style="color:#00007F">Then</SPAN> HighNumber = HowManyVals * 100<br>            HighNumber = Application.Min(HighNumber, ULIMIT)<br>            <br>            <SPAN style="color:#00007F">If</SPAN> Target.Areas.Count > 1 _<br>            <SPAN style="color:#00007F">Or</SPAN> (Target.Cells.Count< HowManyVals And Target.Cells.Count > 1)<SPAN style="color:#00007F">Then</SPAN><br>                MULTIRAND = "BOTCHED"<br>                <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Function</SPAN><br>            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>            <br>            <SPAN style="color:#00007F">ReDim</SPAN> aryVals(1<SPAN style="color:#00007F">To</SPAN> Target.Rows.Count, 1<SPAN style="color:#00007F">To</SPAN> Target.Columns.Count)<br>            aryTemp = RANDVALS_RET(LowNumber, HighNumber, HowManyVals)<br>            <SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> aryTemp(LBound(aryTemp) To _<br>                                  <SPAN style="color:#00007F">UBound</SPAN>(aryTemp) + _<br>                                    (Target.Cells.Count - _<br>                                        (UBound(aryTemp) -<SPAN style="color:#00007F">LBound</SPAN>(aryTemp) + 1)))<br>            <br>            i =<SPAN style="color:#00007F">LBound</SPAN>(aryTemp)<br>            <SPAN style="color:#00007F">For</SPAN> x =<SPAN style="color:#00007F">LBound</SPAN>(aryVals, 1)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(aryVals, 1)<br>                <SPAN style="color:#00007F">For</SPAN> y =<SPAN style="color:#00007F">LBound</SPAN>(aryVals, 2)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(aryVals, 2)<br>                    <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> aryTemp(i) =<SPAN style="color:#00007F">Empty</SPAN><SPAN style="color:#00007F">Then</SPAN><br>                        aryVals(x, y) = aryTemp(i)<br>                        i = i + 1<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                        aryVals(x, y) = vbNullString<br>                    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN><br>            <br>            MULTIRAND = aryVals<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> HowManyVals = 0<SPAN style="color:#00007F">Then</SPAN> HowManyVals = 1<br>            <SPAN style="color:#00007F">If</SPAN> HighNumber = 0<SPAN style="color:#00007F">Then</SPAN> HighNumber = HowManyVals * 100<br>            HighNumber = Application.Min(HighNumber, ULIMIT)<br>            <br>            MULTIRAND = Join(RANDVALS_RET(LowNumber, HighNumber, HowManyVals), ", ")<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Dim1_Ubound > 0 And Dim2_UBound > 0<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">ReDim</SPAN> aryVals(1<SPAN style="color:#00007F">To</SPAN> Dim1_Ubound, 1<SPAN style="color:#00007F">To</SPAN> Dim2_UBound)<br>            HowManyVals = Dim1_Ubound * Dim2_UBound<br>            <SPAN style="color:#00007F">If</SPAN> HighNumber = 0<SPAN style="color:#00007F">Then</SPAN> HighNumber = HowManyVals * 100<br>            HighNumber = Application.Min(HighNumber, ULIMIT)<br>            <br>            aryTemp = RANDVALS_RET(LowNumber, HighNumber, HowManyVals)<br>            <br>            i =<SPAN style="color:#00007F">LBound</SPAN>(aryTemp)<br>            <SPAN style="color:#00007F">For</SPAN> x =<SPAN style="color:#00007F">LBound</SPAN>(aryVals, 1)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(aryVals, 1)<br>                <SPAN style="color:#00007F">For</SPAN> y =<SPAN style="color:#00007F">LBound</SPAN>(aryVals, 2)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(aryVals, 2)<br>                    aryVals(x, y) = aryTemp(i)<br>                    i = i + 1<br>                <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN><br>            <br>            MULTIRAND = aryVals<br>        <SPAN style="color:#00007F">ElseIf</SPAN> Dim1_Ubound > 0<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">ReDim</SPAN> aryVals(1<SPAN style="color:#00007F">To</SPAN> Dim1_Ubound)<br>            HowManyVals = Dim1_Ubound<br>            <SPAN style="color:#00007F">If</SPAN> HighNumber = 0<SPAN style="color:#00007F">Then</SPAN> HighNumber = HowManyVals * 100<br>            HighNumber = Application.Min(HighNumber, ULIMIT)<br>            <br>            MULTIRAND = RANDVALS_RET(LowNumber, HighNumber, HowManyVals)<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> HowManyVals > HighNumber - LowNumber + 1<SPAN style="color:#00007F">Then</SPAN><br>                MULTIRAND = vbNullString<br>                <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Function</SPAN><br>            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> HowManyVals = 0<SPAN style="color:#00007F">Then</SPAN> HowManyVals = 1<br>            <SPAN style="color:#00007F">If</SPAN> HighNumber = 0<SPAN style="color:#00007F">Then</SPAN> HighNumber = HowManyVals * 100<br>            HighNumber = Application.Min(HighNumber, ULIMIT)<br>            <br>            MULTIRAND = Join(RANDVALS_RET(LowNumber, HighNumber, HowManyVals), ", ")<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Function</SPAN> RANDVALS_RET(<SPAN style="color:#00007F">ByVal</SPAN> LowNumber<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>                              <SPAN style="color:#00007F">ByVal</SPAN> HighNumber<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>                              <SPAN style="color:#00007F">ByVal</SPAN> HowManyVals<SPAN style="color:#00007F">As</SPAN> Long _<br>                              )<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br>Dim _<br>i              <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>lVal            <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>lTemp          <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, _<br>ary            <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>, _<br>aryVals        <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br>    <br>    <SPAN style="color:#00007F">ReDim</SPAN> ary(LowNumber<SPAN style="color:#00007F">To</SPAN> HighNumber)<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(ary)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(ary)<br>        ary(i) = i<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    Randomize<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = HighNumber<SPAN style="color:#00007F">To</SPAN> LowNumber + 1<SPAN style="color:#00007F">Step</SPAN> -1<br>        lVal = Int(Rnd() * (i - LowNumber + 1)) + LowNumber<br>        lTemp = ary(lVal)<br>        ary(lVal) = ary(i)<br>        ary(i) = lTemp<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    <SPAN style="color:#00007F">ReDim</SPAN> aryVals(1<SPAN style="color:#00007F">To</SPAN> HowManyVals)<br>    lVal = 0<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = LowNumber<SPAN style="color:#00007F">To</SPAN> LowNumber + HowManyVals - 1<br>        lVal = lVal + 1<br>        aryVals(lVal) = ary(i)<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    RANDVALS_RET = aryVals<br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

To use as a UDF:
Excel Workbook
A
1182
2140
370
4112
588
640
748
8184
911
10169
Sheet2
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Please note you can adjust lowermost value, highest, number of rand vals to return. I just used ten cells to keep the post sized...

Hope that helps,

Mark
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi barissh

In your code set the Find to look for entire cell values:

Code:
        Set x = Range("A1:A30").Find(num[COLOR=red], LookAt:=xlWhole[/COLOR])
 
L

Legacy 14611

Guest
Try
Code:
Sub randomz()
Dim n, a, i, x,y
n = 29
ReDim a(1 To n, 1 To 1)
For i = 1 To n: a(i, 1) = i: Next i
For i = 1 To n
    x = Fix(Rnd * (n)) + 1
    y = a(i, 1)
    a(i, 1) = a(x, 1)
    a(x, 1) = y
Next i
Range("A1").Resize(n, 1) = a
End Sub
 

barissh

Board Regular
Joined
Aug 10, 2006
Messages
94

ADVERTISEMENT

Dear All,

Many thanks for your kind assist.

GTO = many thanks fr yr looonngg codes. To be honest, I am confused with it
pgc01 = sorry but it doesn't work.
rugila = Many many thanks fr yr help. Yr macro works as I want. That's it :)

Meanwhile I found what is problem with my previous code.

num value should be higher than for next value. But this time it generates 1 number missing. I mean, my purpose is to generate 30 random not equal numbers between 1 -30. If num value gets higher than for next value then when makro generates 31, there will be 1 number missing between 1-30.

Anyway, rugila 's makro is working perfectly. My problem has been fixed.

Many thanks fr everyone again.
 
L

Legacy 14611

Guest
Thanks for feedback.

Maybe minor point, but I'd be a bit happier if you used this version instead. You may not note any difference, but ...
Code:
Sub randomz2()
Dim n, a, i, x, y
n = 29
ReDim a(1 To n, 1 To 1)
For i = 1 To n: a(i, 1) = i: Next i
For i = 1 To n
    x = i + Fix(Rnd * (n - i + 1))
    y = a(i, 1)
    a(i, 1) = a(x, 1)
    a(x, 1) = y
Next i
Range("A1").Resize(n, 1) = a
End Sub
 

barissh

Board Regular
Joined
Aug 10, 2006
Messages
94
Dear rugila,

I don't know what is the difference but as per your advise I will use randomz2.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top