CODEWITHSUNDEEP
vbaexcel
Naina
Naina

Reputation: 43

Extracting the item from a DICTIONARY in VBA

I have created a dictionary that stores the sheet name and its number.

So the code is as:

Sub SetDiction()

Dim num as Excel.Range
Dim wks As Excel.Worksheet

For each wks in ThisWorkbook.Worksheets
Set num = Nothing
Set num = wks.Range("SheetNumber")
If not(num is Nothing) Then
rModule.rDictionary.Add:=num.Value Item:=wks
End If

Next End Sub

Now am trying to get the wks name and put it in another worksheet.The code is:

Sub GetSheet()
Dim key as variant

For each key in rModule.rDictionary.Keys
With Sheet2
  .Cells(2,1).Value = rModule.rDictionary.Item(key)
End With
End Sub

It is giving me application defined or Object-defined error. Can some one help please?

Upvotes: 0

Views: 924

Answers (1)

Alex K.
Alex K.

Reputation: 175776

Your .Add call is syntactically incorrect and you are adding the whole worksheet to the dictionary where presumably you just want to add the named range, change to:

rDictionary.Add num.Value, num

To add the name:

rDictionary.Add num.Value, wks.Name

Upvotes: 1

Related Questions