Bug in the code I posted. Use the following instead:

Function RandsFromRange(InputRange As Range, GetNum As Long) As Variant
Dim ResultArr() As Variant
Dim SourceArr() As Variant
Dim TopNdx As Long
Dim ResultNdx As Long
Dim SourceNdx As Long
Dim Temp As Variant

If InputRange.Columns.Count 1 And InputRange.Rows.Count 1 Then
RandsFromRange = CVErr(xlErrRef)
Exit Function
End If

If GetNum InputRange.Cells.Count Then
RandsFromRange = CVErr(xlErrValue)
Exit Function
End If

ReDim ResultArr(1 To InputRange.Cells.Count)
SourceArr = InputRange.Value
Randomize
TopNdx = UBound(ResultArr)
For ResultNdx = LBound(ResultArr) To UBound(ResultArr)
SourceNdx = Int(TopNdx * Rnd + 1)
ResultArr(ResultNdx) = SourceArr(SourceNdx, 1)
Temp = SourceArr(SourceNdx, 1)
SourceArr(SourceNdx, 1) = SourceArr(TopNdx, 1)
SourceArr(TopNdx, 1) = Temp
TopNdx = TopNdx - 1
Next ResultNdx

If IsObject(Application.Caller) = True Then
If TypeOf Application.Caller Is Excel.Range Then
If Application.Caller.Columns.Count = 1 Then
RandsFromRange = Application.Transpose(ResultArr)
Else
RandsFromRange = ResultArr
End If
Else
' do nothing
End If
Else
RandsFromRange = ResultArr
End If

End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Chip Pearson" wrote in message
...
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