Reputation: 67
Is it possible to extract the nth key from a dictionary and its value in VBA? Something like
For i = 1 To Counter + diccompare.Count - UBound(RecSource)
WS.Cells(Lastrow + i, "A") = diccompare.Keys(UBound(RecSource) - Counter + i)
Next
Where I am trying to assign the Cell(Lastrow +i) the value of the key in dictionary diccompare(UBound(RecSource) - Counter + i)
Upvotes: 4
Views: 3880
Reputation: 29421
you could use this helper Function:
Function GetNthKey(dict As Dictionary, nth As Long)
Dim arr As Variant
With dict
arr = .Keys
GetNthKey = arr(nth - 1)
End With
End Function
to be exploited in your "main" code as follows:
Dim diccompare As Dictionary
Set diccompare = New Dictionary
With diccompare
.Add 1, "a"
.Add 2, "b"
.Add 3, "c"
.Add 4, "d"
End With
MsgBox GetNthKey(diccompare, 2) '<--| returns "2", i.e. the 2nd key
Upvotes: 3
Reputation: 8531
Not sure if I fully got you, something like this
Sub KeyTest()
Dim d As New Scripting.Dictionary
d.Add "Test1", 1
d.Add "Test2", 2
d.Add "Test3", 99
Debug.Print d.Keys()(1)
End Sub
Upvotes: 6