Given red, Red, Blue, blue, blue in A1:A5,

Dim x as New Collection
dim rng as Range
Set rng = Range("A1:A5")
On Error Resume Next
For each iVal in rng
x.Add Item: = iVal, key:= CStr(iVal)
Next

will produce a two-element collection of red and Blue. How can I make
the collection of unique items case sensitive; i.e., a 4-element
collection of red, Red, Blue, blue?

Thanks in advance.

Alan Beban

Hello Alan,

You can do it with a Dictionary object...
'--------------------------------
'Requires project reference to the "Microsoft Scripting Runtime"
Sub Test()
Dim x As Scripting.Dictionary
Dim Rng As Range
Dim iVal As Range

Set x = New Scripting.Dictionary
Set Rng = Range("A1:A5")

On Error Resume Next
For Each iVal In Rng
x.Add key:=iVal.Text, Item:=iVal
Next
On Error GoTo 0
Range(Cells(1, 2), Cells(1, x.Count + 1)).Value = x.Keys

Set Rng = Nothing
Set iVal = Nothing
Set x = Nothing
End Sub
'--------------------------------

Regards,
Jim Cone
San Francisco, CA

"Alan Beban" wrote in message ...
Given red, Red, Blue, blue, blue in A1:A5,

Dim x as New Collection
dim rng as Range
Set rng = Range("A1:A5")
On Error Resume Next
For each iVal in rng
x.Add Item: = iVal, key:= CStr(iVal)
Next

will produce a two-element collection of red and Blue. How can I make
the collection of unique items case sensitive; i.e., a 4-element
collection of red, Red, Blue, blue?
Thanks in advance.
Alan Beban

Hi Jim,

Thanks. I wouldn't have worked it out on my own.

Alan Beban

Jim Cone wrote:

Hello Alan,

You can do it with a Dictionary object...
'--------------------------------
'Requires project reference to the "Microsoft Scripting Runtime"
Sub Test()
Dim x As Scripting.Dictionary
Dim Rng As Range
Dim iVal As Range

Set x = New Scripting.Dictionary
Set Rng = Range("A1:A5")

On Error Resume Next
For Each iVal In Rng
x.Add key:=iVal.Text, Item:=iVal
Next
On Error GoTo 0
Range(Cells(1, 2), Cells(1, x.Count + 1)).Value = x.Keys

Set Rng = Nothing
Set iVal = Nothing
Set x = Nothing
End Sub
'--------------------------------

Regards,
Jim Cone
San Francisco, CA

"Alan Beban" wrote in message ...

Given red, Red, Blue, blue, blue in A1:A5,

Dim x as New Collection
dim rng as Range
Set rng = Range("A1:A5")
On Error Resume Next
For each iVal in rng
x.Add Item: = iVal, key:= CStr(iVal)
Next



will produce a two-element collection of red and Blue. How can I make
the collection of unique items case sensitive; i.e., a 4-element
collection of red, Red, Blue, blue?
Thanks in advance.
Alan Beban

Alan,

You probably would find the Windows Script documentation file of interest - available from...

http://msdn.microsoft.com/library/de...ist/webdev.asp
Microsoft Windows Script 5.6 (Windows 2000, XP)

Regards,
Jim Cone
San Francisco, CA

"Alan Beban" wrote in message ...
Hi Jim,
Thanks. I wouldn't have worked it out on my own.
Alan Beban

Jim Cone wrote:
Hello Alan,
You can do it with a Dictionary object...
'--------------------------------
'Requires project reference to the "Microsoft Scripting Runtime"
Sub Test()
Dim x As Scripting.Dictionary
Dim Rng As Range
Dim iVal As Range
Set x = New Scripting.Dictionary
Set Rng = Range("A1:A5")
On Error Resume Next
For Each iVal In Rng
x.Add key:=iVal.Text, Item:=iVal
Next
On Error GoTo 0
Range(Cells(1, 2), Cells(1, x.Count + 1)).Value = x.Keys
Set Rng = Nothing
Set iVal = Nothing
Set x = Nothing
End Sub
'--------------------------------
Regards,
Jim Cone
San Francisco, CA

"Alan Beban" wrote in message ...

Given red, Red, Blue, blue, blue in A1:A5,
Dim x as New Collection
dim rng as Range
Set rng = Range("A1:A5")
On Error Resume Next
For each iVal in rng
x.Add Item: = iVal, key:= CStr(iVal)
Next
will produce a two-element collection of red and Blue. How can I make
the collection of unique items case sensitive; i.e., a 4-element
collection of red, Red, Blue, blue?
Thanks in advance.
Alan Beban

Thanks.

Jim Cone wrote:

Alan,

You probably would find the Windows Script documentation file of interest - available from...

http://msdn.microsoft.com/library/de...ist/webdev.asp
Microsoft Windows Script 5.6 (Windows 2000, XP)

Regards,
Jim Cone
San Francisco, CA

"Alan Beban" wrote in message ...

Hi Jim,
Thanks. I wouldn't have worked it out on my own.
Alan Beban


Jim Cone wrote:

Hello Alan,
You can do it with a Dictionary object...
'--------------------------------
'Requires project reference to the "Microsoft Scripting Runtime"
Sub Test()
Dim x As Scripting.Dictionary
Dim Rng As Range
Dim iVal As Range
Set x = New Scripting.Dictionary
Set Rng = Range("A1:A5")
On Error Resume Next
For Each iVal In Rng
x.Add key:=iVal.Text, Item:=iVal
Next
On Error GoTo 0
Range(Cells(1, 2), Cells(1, x.Count + 1)).Value = x.Keys
Set Rng = Nothing
Set iVal = Nothing
Set x = Nothing
End Sub
'--------------------------------
Regards,
Jim Cone
San Francisco, CA



"Alan Beban" wrote in message ...



Given red, Red, Blue, blue, blue in A1:A5,
Dim x as New Collection
dim rng as Range
Set rng = Range("A1:A5")
On Error Resume Next
For each iVal in rng
x.Add Item: = iVal, key:= CStr(iVal)
Next
will produce a two-element collection of red and Blue. How can I make
the collection of unique items case sensitive; i.e., a 4-element
collection of red, Red, Blue, blue?
Thanks in advance.
Alan Beban