PDA

View Full Version : [SOLVED] Randomly select from a range



babsc01
01-19-2005, 12:45 PM
Howdy...this will probably be an easy one. I have a range of cells containing numbers. I want to randomly select a certain quantity of those numbers, non-repeating, and display the result in another range.

Any VBA or formula ideas? Thanks!

Richie(UK)
01-19-2005, 01:09 PM
Hi b,

Tushar Mehta has some useful routines here:

http://www.tushar-mehta.com/excel/newsgroups/rand_selection/vba.html

mvidas
01-19-2005, 01:17 PM
Hi babsc01,

I didn't actually look at Richie's link, so a similar thing may be there, but you could also use a sub like:


Sub LetsGiveItATry()
Dim InputRange As Range, OutputRange As Range, CLL As Range
Dim CellsColl As New Collection, i As Long, j As Long
Set InputRange = Range("A1:A35")
Set OutputRange = Range("B1:B9")
If OutputRange.Cells.Count > InputRange.Cells.Count Then
Msgbox "Output range bigger than input range. Please re-define."
Exit Sub
End If
For Each CLL In InputRange.Cells
CellsColl.Add CLL
Next CLL
Randomize
For i = 1 To OutputRange.Cells.Count
j = Int(Rnd() * CellsColl.Count + 1)
OutputRange.Cells(i) = CellsColl(j)
CellsColl.Remove j
Next
End Sub

Just define the InputRange and the OutputRange, should take care of it all for you.
Matt

babsc01
01-21-2005, 10:58 AM
This will work...thanks so much, Matt.

mvidas
01-21-2005, 11:15 AM
Glad to hear it! You can mark it as Solved using "Thread Tools" right above your first message. That way everyone will know you're all set.

This thread must have been edited to remove the Randomize discussion. That's too bad, it was nice seeing people's views trying to figure out how excel works.

babsc01
01-21-2005, 11:23 AM
I think it was deleted, too. Almost seemed like it was turning into a pretty heated discussion.

mvidas
01-21-2005, 11:37 AM
My favorite kind :)

Aaron Blood
01-21-2005, 01:45 PM
I think it was deleted, too. Almost seemed like it was turning into a pretty heated discussion....not heated. Too much caffeine in the afternoon I think.

The ending was cool though... Involved light sabers and acrobatics.

Some errant force lightning must've zapped a few of the innocently bystanding postings. It happens sometimes when the Jedi clash.