All you need to do to use the code is:

'In the VBA Editor, click on
'ToolsReferencesMicrosoft Scripting RuntimeOK

then copy and paste my code into a standard module


"William Benson" wrote:

Thanks, will look it up and see if there is a way I can use it.

"cush" wrote in message
...
Further:
I am not totally sure of this but I think the Collection object is part of
Excel's object model, whereas Dictionary is not and requires a reference
to
MS Scripting Runtime (a dll) if you want to write code using this object.
The end user is NOT required to have such a reference.

Also, the properties and methods are a little different.
Google on Dictionary for more info.

"William Benson" wrote:

Cush,

I haven't heard of dictionary, can you tell me how dictionary differs
from
collection different?

This line: Uniques(i) = oCell.Value

is going to mean a value gets entered in the uniques column whether or
not
it had occurred prior, right?

Sorry, it is late for me, I may not be thinking right.
"cush" wrote in message
...
If I were doing this I would first insert a col B (later to be hidden)
where
I would consolidate all the data from colA (getting rid of blanks and
duplicates) with the following macro:

Sub CreateUniqueList
'Requires a reference to MS Scripting Runtime
'In the VBA Editor, click on
'ToolsReferencesMicrosoft Scripting Runtime
''''''''''''''''''''''''''''''''''''''''''''''

Dim Dict As Scripting.Dictionary
Dim i As Integer
Dim oCell As Range
Dim Source As Range
Dim Uniques As Range

Set Source = Range("A:A")
Set Uniques = Range("B:B")

Set Dict = New Dictionary
i = 1
With Dict
For Each oCell In Source
If Not .Exists(oCell.Value) Then
If Not oCell.Value = "" Then
.Add Key:=oCell.Value, Item:=i
Uniques(i) = oCell.Value
i = i + 1

End If
End If
Next oCell
End With

Shutdown:
Set Dict = Nothing

End Sub

I would then change my original formula from A:A to B:B

=INDEX(B:B,ROUND(RAND()*COUNTA(B:B),0))

This would clean it up and avoid problems with blanks.

"William Benson" wrote:

User enters any array of numbers beginning in any cell: Example

A15 23
A16 11
A17 5
. .
. .
. .
A172 145
A173 220


Required: Formula (not VBA, but perhaps array-entered) which will:

1) return a RANDOM number from AMONG the items in the list
2) Not require that the list start, list end, or # of items in
the
list be fixed (only col A is fixed).
3) not require that the list be in ascending or descending
order
3) be entered in cell B1

I started something involving INDEX and the ROW() of the starting and
ending
item, but I want it to be more dynamic. Then I tried to use MATCH to
tell
me
the start or end values, by using 0.001 and 1,000,000 and matching
with
Match_Type = 1, or -1, but that required the items be in ascending or
descending order. So, I am stuck... can it be done?