Solved

Picking from a Random List Without Repetition in vba

Posted on 2011-10-11
Medium Priority
282 Views
On VBA I have two arrays, a() and b()
a() is filled with a set of values from an excel spreadsheet
b() is filled by selecting random numbers from a().
I want b() to be filled by selecting random numbers from a() without repetition.
Right now I am using a function like the following
b(z, J) = a(Int(((totalvalues - 6) - 1 + 1) * Rnd + 1), J)
How would i do the same thing and ensure there are no repetitons?
0
Question by:awesomejohn19
• 2

LVL 51

Expert Comment

ID: 36950175
Using code like the following you can check to see if an item is in b() and not add it again if it is.

``````Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Private Const LB_FINDSTRINGEXACT = &H1A2
Private Sub Command1_Click()

If (SendMessage(List1.hwnd, LB_FINDSTRINGEXACT, -1&, _
ByVal CStr(strSomeValue))) > -1& Then
Else
End If
``````
0

LVL 51

Expert Comment

ID: 36950184
I'm sorry, ignore that.
0

LVL 81

Expert Comment

ID: 36950191
Create a third array c() filled with random numbers, same dimension as a(). Populate b array by picking the n smallest values in c, drawing values from the a array with corresponding index numbers.

``````Sub RandomPicker()
Dim a(20) As Variant, b(5) As Variant
Dim c() As Double, d As Double
Dim i As Long, j As Long, k As Long, n As Long, nPicks As Long
n = UBound(a)
ReDim c(n)
nPicks = UBound(b)

For i = LBound(c) To n
a(i) = Chr(64 + i)  'Populate a array
c(i) = Rnd()        'Populate c array
Next
For i = LBound(b) To nPicks
k = i - LBound(b) + 1   'Index reference starting with 1
d = Application.Small(c, k)
j = Application.Match(d, c, 0)
b(i) = a(j)
Debug.Print b(i)       'Print value chosen in Immediate pane
Next
End Sub
``````
0

LVL 19

Accepted Solution

Arno Koster earned 2000 total points
ID: 36950210
you could copy the valies of array A into a temporarily array C
then select a randon number from 0 to the length of array C
move the selected item from C to B (copy it from C to B, delete from array C)
continue until C is empty.

When you do not want to write the 'delete from array' function yourself, you could opt to implement the array C as an collection :

dim c as collection

c.remove(random selected item number)

blogspot forum
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses
Course of the Month5 days, 7 hours left to enroll