Solved

# Randomize values in a collection

Posted on 2009-12-31
340 Views
Hello, Does anyone know of a quick way to randomize the values in a collection?  I have a collection of 400 cell addresses (A1 through U2) that I need to randomize.

-Thanks,
0
Question by:JamesCbury

LVL 81

Accepted Solution

Public Sub RandomizeCollection()

Dim SortedValues As Collection
Dim RandomValues As Collection
Dim Index As Long

Set SortedValues = New Collection
For Index = 1 To 10
Next Index

Set RandomValues = New Collection
For Index = 1 To SortedValues.Count
Next Index

End Sub

ByVal Value As Variant, _
ByVal Values As Collection _
)

' Add the parameter Value to the collection in a random position.

Dim Index As Long

Randomize

Index = Rnd * (Values.Count + 1) + 1
If Index > Values.Count Then
Else
End If

End Sub

Kevin
0

Author Closing Comment

Very cool
0

LVL 85

Expert Comment

Another one...
``````Option Explicit

Private Sub CommandButton1_Click()

Dim col As New Collection

' etc.

Dim itm As Variant

Debug.Print "Original:"

For Each itm In col

Debug.Print itm

Next

ShuffleCollection col

Debug.Print "Shuffled:"

For Each itm In col

Debug.Print itm

Next

End Sub

Private Sub ShuffleCollection(ByVal C As Collection)

Static seeded As Boolean

If Not seeded Then

seeded = True

Randomize Timer

End If

Dim c2 As New Collection

While C.Count > 0

C.Remove 1

Wend

Dim index As Long

While c2.Count > 0

index = Int((c2.Count - 1 + 1) * Rnd + 1)

c2.Remove index

Wend

End Sub
``````
0

## Featured Post

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.