Randomly choose x numbers from y data block
 

I need a macro to select at random x numbers (non-repeating) from a matrix of
y cells and store them in an array.

Randomly choose x numbers from y data block
 

See this site:

http://www.ozgrid.com/VBA/RandomNumbers.htm


"Steve" wrote in message
...
I need a macro to select at random x numbers (non-repeating) from a matrix
of
y cells and store them in an array.

Randomly choose x numbers from y data block
 

Hi Steve,

The following code has 2 input boxes to allow you to select the matrix cells
and specify the number of random numbers.

The MsgBox simply establishes that the random numbers are in the Array.

The code uses a worksheet (Sheet2) to temporarily store the random
selections so that countif can be used to determine if random number has
already been used. Edit this sheet name if necessary.

You might need the Analysis Toolpak Add-In if not already installed for the
RANDBETWEEN function to work. (Analysis Toolpak is a standard Add-In feature
of Excel. See Help for how to install.)

The code will teminate if it experiences difficulty creating the required
number of unique random numbers from the matrix and the number of elements
requested.

Sub RandomNumbersArray()

Dim wsOutput As Worksheet
Dim rngMyMatrix As Range
Dim varElements As Variant
Dim i As Long
Dim lngRndCount As Long
Dim rndNumb As Long
Dim MyArray()

'Edit Sheet2 to your required temporary
'Storage sheet for the random numbers.
Set wsOutput = Sheets("Sheet2")

On Error Resume Next
Set rngMyMatrix = Application.InputBox _
(prompt:="Select number matrix", _
Title:="Matrix selection", Type:=8)
On Error GoTo 0

If rngMyMatrix Is Nothing Then
MsgBox "User cancelled." & vbCrLf & _
"Processing terminated."
Exit Sub
End If

varElements = Application.InputBox _
(prompt:="How many numbers required?", _
Title:="Number of elements", _
Default:=20, Type:=1)

If varElements = False Then
MsgBox "User cancelled." & vbCrLf & _
"Processing terminated."
Exit Sub
End If

wsOutput.Columns("A:A").ClearContents

wsOutput.Cells(1, 1) = "Rnd List"

With rngMyMatrix
For i = 1 To varElements
lngRndCount = 0

StartRandSelect:

rndNumb = WorksheetFunction _
.RandBetween(1, .Cells.Count)

If WorksheetFunction _
.CountIf(wsOutput.Columns("A:A"), _
.Cells(rndNumb)) = 0 Then

wsOutput.Cells(Rows.Count, "A") _
.End(xlUp).Offset(1, 0) _
= .Cells(rndNumb)
Else
lngRndCount = lngRndCount + 1
If lngRndCount 10 Then
MsgBox "Difficulty creating " & _
"required number of random numbers." _
& vbCrLf & vbCrLf & _
"Processing will terminate."
Exit Sub
Else
GoTo StartRandSelect
End If
End If

Next i
End With

ReDim MyArray(i - 1)

MyArray = wsOutput.Range("A2:A" & i)

For i = 1 To UBound(MyArray)
MsgBox MyArray(i, 1)
Next

End Sub

--
Regards,

OssieMac

Randomly choose x numbers from y data block
 

Hello,

I suggest to use my UDF Random_Pick:
http://sulprobil.com/html/uniqrandint.html

Regards,
Bernd

Randomly choose x numbers from y data block
 

See http://www.cpearson.com/Excel/randomNumbers.aspx , specifically
the section "Unique Random Longs". The code there will return an array
of N non-repeating random longs between X and Y. You can download a
bas module with the code from the page at
http://www.cpearson.com/Zips/modRandomNumbers.zip .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 18 Jul 2009 19:21:01 -0700, Steve
wrote:

I need a macro to select at random x numbers (non-repeating) from a matrix of
y cells and store them in an array.

Randomly choose x numbers from y data block
 

Adding one more to the alternatives already posted {grin}
Select elements at random without repetition
http://www.tushar-mehta.com/excel/ne...ion/index.html


On Sat, 18 Jul 2009 19:21:01 -0700, Steve
wrote:

I need a macro to select at random x numbers (non-repeating) from a matrix of
y cells and store them in an array.

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products