Dictionary Items

[Rocky0201]

Hi...

I have built several items in my dictionary called Tdic.

TDic("MSL")
TDic("MS")
.
.
etc

If I wanted to know how many iems are in Tdic, I can do:
DicCount = Tdic.count

I'm having problems determining what name I gave at each item in Tdic.

I've tried:

TName = Tdic.Item(1) the result in Tname is Empty

There are a number of iterations that I tried to no avail receiving Complie erros and such.

Is there a way to extract the name of the item? Meaning can I loop through Tdic and load TName with whatever name is first in Tdic for the first loop, Whatever name is in Tdic for the second loop, etc.

Thanks

 

[GTO]

Are you using late-binding? If so, then I believe you'll want to assign the .Keys or .Items collection to an array and retrieve specific elements from the array.

 

[Rocky0201]

Thanks for responding, again, GTO...

Here's how I build the items:

Public Sub BuildTabArray(TabANameCol, TabAIndex)
 
    TabTag = Sheets("VBA Definitions").Cells(RowNumber, TabANameCol)
    TabIndex = Sheets("VBA Definitions").Cells(RowNumber, TabAIndex)
 
    If Tdic.Exists(TabTag) Then
        Tdic(TabTag) = TabIndex
       Else
        Tdic.Add TabTag, 0
        Tdic(TabTag) = TabIndex
    End If
End Sub

Later in my Code I want to loop through the items I defined and compare the name to a value in a series of cells. Once I have a match, I can then isue the index I have stored in a cell in the same row to build my various arrays.

Thanks again...

 

[Rocky0201]

What I am looking to do after I have built my items is something like:

ii = 2
 
For I = 1 to Tdic.Count
[INDENT]If The name of Item 1 of Tdic = Sheets("Sheet1").cells(ii,3) then
(meaning, if the name of Item 1 (Value) i.e "MSL" = a the value in my cell)
[/INDENT][INDENT][INDENT]MyArray(Tdic(The Name I am pointed to)) = Values from Sheet1
(i.e. Tdic("MSL") would contain the Index number that I built earlier)
[/INDENT][/INDENT][INDENT]ii = ii + 1
[/INDENT]Next I

I hope this makes sense. Please let me know if not.

Thanks so very much...

 

[GTO]

Hi Rocky,

I am not sure that I am following, nor am I sure whether your are now getting to retrieve the various Keys/Items from the dictionary. By way of short example, in a new wb, setup like:
JunkTest

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">HDR</TD><TD style="TEXT-ALIGN: center">HDR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Sally</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD>Pedro</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD>Mark</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">0</TD><TD>Mike</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD>Rocky</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Bob</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">10</TD><TD>Peter</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Jon</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Anne</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">9</TD><TD>Tricia</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Option Explicit
 
Sub CallIt()
Dim DIC As Dictionary
Dim RowNum As Long
Dim aryKeys As Variant
Dim aryItems As Variant
 
    Set DIC = New Dictionary
    For RowNum = 2 To 11
        Call BuildTabArray(1, 2, RowNum, DIC)
    Next
 
    aryKeys = DIC.Keys
    aryItems = DIC.Items
 
    For RowNum = LBound(aryKeys) To UBound(aryKeys)
        Debug.Print "Key: " & aryKeys(RowNum) & Space(4 - Len(aryKeys(RowNum))) & _
                    "contains the item: " & aryItems(RowNum)
    Next
End Sub
 
Public Sub BuildTabArray(TabANameCol, TabAIndex, RowNumber, Tdic)
Dim TabTag As Variant
Dim TabIndex As Variant
 
    TabTag = ThisWorkbook.Worksheets("JunkTest").Cells(RowNumber, TabANameCol).Value
    TabIndex = ThisWorkbook.Worksheets("JunkTest").Cells(RowNumber, TabAIndex).Value
 
    If Tdic.Exists(TabTag) Then
        Tdic(TabTag) = TabIndex
       Else
        Tdic.Add TabTag, 0
        Tdic(TabTag) = TabIndex
    End If
End Sub

Now if I am reading correctly, you are populating something like the above? If you run the above, you'll note that certain keys' items get overwritten. Is that what we want?

 

[Rocky0201]

I think you got it GTO!!!!

Here's what I was looking for (I think):

For Each ItemKey In Tdic.Keys

MyTableArray(Tdic(ItemKey)) = Sheets("Sheet1").Cells(ii,4)
ii = ii +1
​

Next ItemKey

This is just and example not the actual code withing the For loop. The Value at Tdic(ItemKey) is the index into the array where I will load my data.

 

[Rocky0201]

opps... Thanks GTO!!!!