Try the following function.
Function RandsFromRange(InputRange As Range, GetNum As Long) As Variant()
Dim Res() As Variant
Dim Ins() As Variant
Dim TopNdx As Long
Dim Ndx As Long
Dim N As Long
ReDim Res(1 To InputRange.Cells.Count)
Ins = InputRange.Value
TopNdx = UBound(Res)
For N = 1 To GetNum
Ndx = Int(TopNdx * Rnd + 1)
Res(N) = Ins(Ndx, 1)
TopNdx = TopNdx - 1
Next N
If IsObject(Application.Caller) = True Then
If TypeOf Application.Caller Is Excel.Range Then
If Application.Caller.Columns.Count = 1 Then
RandsFromRange = Application.Transpose(Res)
Else
RandsFromRange = Res
End If
Else
' do nothing
End If
Else
RandsFromRange = Res
End If
End Function
You can call it from a worksheet cells with a formula like
=RandsFromRange(A1:A10,5) where A1:A10 is the range of values to pick from
and 5 is the number of value to return in random order. Modify the
parameters to suit your needs. See also
http://www.cpearson.com/Excel/randomNumbers.aspx . Values will be taken from
A1:A10 with no repeats in the returned set (assuming that the source range
A1:A10 has no duplicate entries).
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Richard Champlin" wrote in
message ...
Is it possible to create a random number generator that searches a list of
integers that are not consecutive, without generating a number that is not
in
the list?
I would use it to randomly pick "X" number of items in a list to check for
inventory status, without having to verify that the random number
generated
actually exists.
--
Richard Champlin
Administrative Program Assistant II
Children's Hospital & Regional Medical Center, Seattle