Be aware, I am a novice when it comes to writing routines. I recently
started creating macros, then debugging using Visual Basic. I am unfamiliar
with the language syntax. Could I take the subroutine you provided and
simply paste it into a new VB document to create a routine?
--
Richard Champlin
Administrative Program Assistant II
Children''''''''s Hospital & Regional Medical Center, Seattle


"Steve Yandl" wrote:

Richard,

The sample subroutine below limits the random numbers to integers between 0
and 1000 but you can edit the high and low values. It looks at a list of
integers in Column A on the active sheet and generates random numbers in the
allowed range until a random number is created that isn't already recorded
in Column A. Finally, it adds the new random integer in the next available
cell at the bottom of Column A.

___________________________________

Sub NewRandInteger()
Dim rowTop As Integer
Dim rngInRowA As Range
Dim intRand As Integer
Dim Low As Double
Dim High As Double
Dim R As Double

Set objDict = CreateObject("Scripting.Dictionary")

rowTop = Range("A65536").End(xlUp).Row
Set rngInRowA = Range("A1:A" & rowTop)

For Each myCell In rngInRowA.Cells
intRand = myCell.Value
If Not objDict.Exists(intRand) Then
objDict.Add intRand, intRand
End If
Next myCell

High = 1000
Low = 0

R = Range("A1").Value

Do Until Not objDict.Exists(R)
R = Int((High - Low + 1) * Rnd() + Low)
Loop

Range("A" & rowTop + 1).Value = R

End Sub

____________________________________

Steve



"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