Printable View
Here's a way to generate unique random numbers from a minimum value to a maximum value
Code:Sub GenerateRandomUnique()
Dim lng As Long
Const lngMax As Long = 100
Const lngMin As Long = 1
With CreateObject("Scripting.Dictionary")
Do While .Count <> lngMax
lng = Rnd * (lngMax - lngMin + 1) + 1
.Item(lng) = Empty
Loop
MsgBox Join(.Keys, " ")
End With
End Sub
An old thread: http://www.excelfox.com/forum/f13/cr...rator-vba-245/
Assuming you meant the "from a minimum to a maximum value" to mean the user can set those values to their own values, I think your code is not correct. I believe this modification to it will perform correctly...Quote:
Originally Posted by Excel Fox
Code:Sub GenerateRandomUnique() Dim lng As Long
Const lngMax As Long = 100
Const lngMin As Long = 1
With CreateObject("Scripting.Dictionary")
Do While .Count <= lngMax - lngMin 'removed the greater than symbol
lng = Rnd * (lngMax - lngMin) + lngMin 'removed the +1
.Item(lng) = Empty
Loop
MsgBox Join(.Keys, " ")
End With
End Sub
Thanks Rick for correcting that.
Similar discussions by Rajan at Get Random List : | LinkedIn
and
Get Random List : « excelpoweruser
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Here is something I originally posted to the old compiled VB newsgroups back in 1999 (according to the acticle header) which was subsequently picked up by this compiled VB website. My code is the subroutine named RandomizeArray embedded within the "housing" the website owner put it in...Quote:
Originally Posted by Excel Fox
[VBnet Helpers] Pure VB: Generating a Random Array of Unique Numbers
Actually, instead of making you wade through the demo code at the above site, here is one of the few ways I have presented my randomizing routine in the past...Quote:
Originally Posted by Rick Rothstein
The following is a generalized shuffling routine that I have posted in the past over in the compiled VB newsgroups, but it works fine in the VBA world of Excel as well. Assign your values to an array and then pass that into the RandomizeArray subroutine and it will put elements of the array into a random order and return the randomized elements back in the original array that was passed to it. So, to read out the randomize list, just read the array from its lower bound to whatever number of random elements you need (up to the maximum of the array's upper bound). It only visits *each* array element *once* so it is quick. The code takes care of running the Randomize statement one time only (which is all that is necessary).
After passing your array into the RandomizeArray subroutine, its elements will be randomly reordered. The passed array may be of any normal type -- integer, string, single, etc. The neat thing is, if you pass an already randomized array to this routine, those randomly ordered elements will be randomize -- sort of like shuffling an already shuffled deck of cards.Code:Sub RandomizeArray(ArrayIn As Variant)
Dim X As Long, RandomIndex As Long, TempElement As Variant
Static RanBefore As Boolean
If Not RanBefore Then
RanBefore = True
Randomize
End If
If VarType(ArrayIn) >= vbArray Then
For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1
RandomIndex = Int((X - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
TempElement = ArrayIn(RandomIndex)
ArrayIn(RandomIndex) = ArrayIn(X)
ArrayIn(X) = TempElement
Next
Else
'The passed argument was not an array, so put error handler here, such as . . .
Beep
End If
End Sub