Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help: Transferring from Dictionary to Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help: Transferring from Dictionary to Array
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help: Transferring from Dictionary to Array
Works great! Thank you very much for your help Jim!
"Jim Cone" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transferring array to range | Excel Programming | |||
Problem transferring array data onto worksheet using Resize | Excel Programming | |||
Transferring part of a multi-dimensional array to a range in VBA | Excel Programming | |||
dictionary | Excel Programming |