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 |
---|
1 | 182 |
---|
2 | 140 |
---|
3 | 70 |
---|
4 | 112 |
---|
5 | 88 |
---|
6 | 40 |
---|
7 | 48 |
---|
8 | 184 |
---|
9 | 11 |
---|
10 | 169 |
---|
|
---|
Excel 2003Entered 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