vba collection - print keys and items

[Mallesh23]

Hi Team,

I am trying to print collections items and key, but unable to do it.
Can you please help me. Want to print collection using both for(i) and for(each) loops.

Data is in Column A and expected output is in Column H.

Option Explicit
Sub Print_Collection_key_Item()

    Dim coll As New Collection
    Dim i As Long
    Dim row As Long

'====Read Data Column 2 and Column 4===
        For i = 2 To 8
            coll.Add Array(Cells(i, 2), Cells(i, 4)), Cells(i, 1)  'add column 2 and column 4 to collection
        Next i

    row = 2
           
'=========Write Data=========  This code works but unable to print key values
    For i = 1 To coll.Count
        'Cells(row, 8).Value = coll(i) ' Print Key
        Cells(row, 9).Value = coll(i)(0) 'Print Item Column 2
        Cells(row, 10).Value = coll(i)(1) 'Print Item Column 4
        row = row + 1
    Next i

''Print Collection using for each

'Range("H1:H8").Value = Application.Index(coll.items, 0, 0) 'Can we use single line code like this
'Range("H1:H8").Value = worksheetfunction.transpose(coll.item) 'like this not sure

        
        row = 2
    Dim itm As Variant
    '=======Write Data using Collection=====  how to print key and items using for each
        For Each itm In coll
            Cells(row, 8).Value = itm
            row = row + 1
        Next Key
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	No of Sixes	No of Fours	Total Scores				Name	No of Sixes	Total Scores
2	Sachin	20	10	90				Sachin	20	90
3	Dhoni	16	15	100				Dhoni	16	100
4	Sehwag	18	14	200				Sehwag	18	200
5	Virat	13	8	150				Virat	13	150
6	Yuvraj	18	20	250				Yuvraj	18	250
7	Hardik	30	25	300				Hardik	30	300
8	Bumrah	2	4	40				Bumrah	2	40
Sheet1

Thanks
mg

 

[RoryA]

You'd have to store the keys as part of the item, or use something like a Dictionary instead.