VBA error 457 key when adding to dictionary-CodePudding

I have an array of combinations called "Keys". I want to check if each combination exists in the column I, and if so, split the existing combination in 2 strings and add them as a pair in my dictionary "combiExist". My code throws a 457 error

Dim combiExist As Object
Set combiExist = CreateObject("Scripting.Dictionary")

For Each cle In keys
'If combination exists in my range
    If Not .Range("I:I").Find(cle) Is Nothing Then
    
        'Split string from 7th position, left part is the key, right part is the value
        combiExist.Add Left(cle, 7), Right(cle, 7)
        
    End If
Next

How can I solve this ?

CodePudding user response：

Error 457 says that the key is already associated with an element of the collection. So, before assigning it to the dictionary make sure it is not there, with .Exists. This one works rather ok, at the end it prints the dictionary:

Sub Main()
    
    Dim combiExist As Object
    Set combiExist = CreateObject("Scripting.Dictionary")
    Dim combinations As Variant
    combinations = Array("joeC12345678910", "C12345678910", "foooooo123")
    Dim cle As Variant
    
    For Each cle In combinations
        If Not Worksheets(1).Range("I:I").Find(cle) Is Nothing Then
            
            If combiExist.exists(Left(cle, 7)) Then
                Debug.Print "Do nothing this one " & (Left(cle, 7)) & " exists!"
            Else
                combiExist.Add Left(cle, 7), Right(cle, 7)
            End If
        End If
    Next

    PrintDictionary combiExist
    
End Sub

Public Sub PrintDictionary(myDict As Object)
    
    Dim key     As Variant
    For Each key In myDict.keys
        Debug.Print key; "-->"; myDict(key)
    Next key
    
End Sub

In general, do not use words like Keys for a variable name, because this one means something in VBA - usually a collection of the keys of a given dictionary. You can see the implementation of myDict.keys in PrintDictionary().