CODEWITHSUNDEEP
exceldictionarytype-mismatchvba
crystalsky
crystalsky

Reputation: 35

type mismatch while iterating through dictionary

I've stumbled upon a problem that i can not resolve. I'm trying to create dictionary like: key: number as a String item: array of Strings

When I'm passing keys to the MsgBox everything is fine but when I want to add Items as well I'm getting type mismatch error...

My code looks like this:

Sub test()

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

Dim records As String
Dim RecordArray() As String

Dim x As Integer
            Application.ScreenUpdating = False
            NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
            Range("A1").Select

For x = 1 To NumRows

                If IsNumeric(Cells(x, 1).Value) Then
                    records = Trim(Cells(x, 2).Value)
                    RecordArray() = Split(records, ",")
                    dict.Add Key:=(Cells(x, 1).Value), Item:=RecordArray()

            ActiveCell.Offset(1, 0).Select

        End If
        Next x


        Application.ScreenUpdating = True

Dim key As Variant
        For Each key In dict.Keys
                MsgBox key
                MsgBox dict(key)
        Next key

End Sub

Data is for example:

A1:2001 B1:  0000101,0000102,0000103
A2:2015 B2:  0000107,0000108
A3:8000 B3:  0000215,0000216,0000217

and so on. Mind that values in column B starts with two spaces before the text. What am I doing wrong here? I'm aware that this code is probably not so good but this is like my first try with VBA :(

Upvotes: 1

Views: 683

Answers (1)

Pawel Czyz
Pawel Czyz

Reputation: 1645

You get type-mismatch becasue you try to put array in msgbox. Try this:

Sub test()

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary
dict.CompareMode = BinaryCompare

Dim records As String
Dim RecordArray() As String

Dim x As Integer
            Application.ScreenUpdating = False
            NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
            Range("A1").Select

For x = 1 To NumRows

                If IsNumeric(Cells(x, 1).Value) Then
                    records = Trim(Cells(x, 2).Value)
                    RecordArray() = Split(records, ",")
                    keyString = (Cells(x, 1).Value)
                    dict.Add key:=keyString, Item:=RecordArray()

            ActiveCell.Offset(1, 0).Select

        End If
        Next x


        Application.ScreenUpdating = True

Dim key As Variant
        For Each key In dict.Keys
                MsgBox key
                i = 0
                For Each Item In dict(key)
                    MsgBox (dict(key)(i))
                    i = i + 1
                Next Item
        Next key

End Sub

I've included a loop to display each item of array stored in dictionary. You could build a string using same method and display it, if you want just one msgbox.

Upvotes: 1

Related Questions