Please Help: Transferring from Dictionary to Array

[Magnivy]

Column A in my worksheet contains repetitive values. I have a macro that
creates a dictionary object and loads it with unique values from the column.
When I try to load the dictionary items into an array, its not working out.
The array does not seem to contain any values (as the msgbox function is not
returning anything). The macro is pasted below.

I cant figure out what I'm doing wrong. Any help would be greatly appreciated.

Thank You!

Magnivy

Sub Macro()

Dim rng As Range
Dim x As Dictionary
Dim arr() As Object
Dim Cell As Object
Dim i As Integer
Dim t As Integer


Set rng = ActiveSheet.Range("A1:A500")

Set x = CreateObject("scripting.dictionary")

On Error Resume Next
For Each Cell In rng.Cells
x.Add Item:=Cell.Value, Key:=CStr(Cell.Value)
Next Cell

ReDim arr(1 To x.Count)

i = 0

For Each Item In x.Items
i = 1 + i
arr(i) = Item
Next Item

For t = 1 To UBound(arr, 1)
MsgBox arr(t)
Next t

End Sub

 

[Jim Cone]

Using a For Each loop on a Dictionary is a little strange.
It is setup differently and the for/each variable must be a variant or object.
Also, the Items method of the Dictionary returns a zero base variant array.
So it can be dumped directly into a Variant variable....
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub Macro()
Dim rng As Range
Dim x As Object
Dim arr() As Variant
Dim Cell As Excel.Range
Dim i As Long
Dim t As Long
Dim vKey As Variant

Set rng = ActiveSheet.Range("C1:C50")
Set x = CreateObject("scripting.dictionary")

On Error Resume Next
For Each Cell In rng.Cells
x.Add Item:=Cell.Value, Key:=CStr(Cell.Value)
Next Cell
On Error GoTo 0

ReDim arr(1 To x.Count)
i = 1

For Each vKey In x
arr(i) = x.Item(vKey)
i = 1 + i
Next 'vkey
'For t = 1 To UBound(arr, 1)
MsgBox arr(6)
'Next t


'An alternate method that should be faster then running a loop.
'varArray is zero based.
Dim varArray As Variant
varArray = x.items
MsgBox varArray(5)

End Sub
'------------------


"Magnivy"
wrote in message
Column A in my worksheet contains repetitive values. I have a macro that
creates a dictionary object and loads it with unique values from the column.
When I try to load the dictionary items into an array, its not working out.
The array does not seem to contain any values (as the msgbox function is not
returning anything). The macro is pasted below.
I cant figure out what I'm doing wrong. Any help would be greatly appreciated.
Thank You!
Magnivy

Sub Macro()
Dim rng As Range
Dim x As Dictionary
Dim arr() As Object
Dim Cell As Object
Dim i As Integer
Dim t As Integer


Set rng = ActiveSheet.Range("A1:A500")

Set x = CreateObject("scripting.dictionary")

On Error Resume Next
For Each Cell In rng.Cells
x.Add Item:=Cell.Value, Key:=CStr(Cell.Value)
Next Cell

ReDim arr(1 To x.Count)

i = 0

For Each Item In x.Items
i = 1 + i
arr(i) = Item
Next Item

For t = 1 To UBound(arr, 1)
MsgBox arr(t)
Next t

End Sub

 

[Magnivy]

Works great! Thank you very much for your help Jim!