VBA error: Run-time error 457

[nightheart]

Hi All,

The code below used to be running fine but suddenly it started giving me the following error: "Run-time error 457:This key is already associated with an element of this collection."

Would you please help with such error as I couldn't find why it is doing that.

Dim x

Set dic = CreateObject("Scripting.dictionary")
    With Sheets("Test")
    For Each r In .Range("a2", .Range("a65536").End(xlUp))
            If r = Me.cmbcategory.Value And r.Offset(, 2) _
                  = Me.cmbname.Value Then
                If Not dic.exists(r.Offset(, 5).Value) Then
                    dic.Add r.Offset(, 5).Value, Nothing
                    [COLOR="Red"]dic.Add r.Offset(, 6).Value, Nothing[/COLOR]
                    Me.cmbprice.AddItem r.Offset(, 5)
                    Me.cmbserial.AddItem r.Offset(, 6)
                    ReDim Preserve m_price(m_long) As Variant
                    ReDim Preserve m_serial(m_long) As Variant
                    m_price(m_long) = r.Offset(, 5)
                    m_serial(m_long) = r.Offset(, 6)
                    m_long = m_long + 1
                 End If
            End If
        Next
    End With
   End Sub

The error pops up at the line in red above.

Thanks

 

[Firefly2012]

You test if r.Offset(,5) already exists but not if r.Offset(,6) exists before you try and add it (so you could simply test if it exists first). If it does exist, what do you want to do though? Just not add it and carry on with the rest of the code, or something else?

 

[nightheart]

Thank you for the reply. Would you please explain more, I think I got lost.

 

[Firefly2012]

This line:

If Not dic.exists(r.Offset(, 5).Value) Then

checks if the value contained in r.Offset(,5) already exists in the dictionary's Keys. If it doesn't already exist, then this line adds it to the Keys:

dic.Add r.Offset(, 5).Value, Nothing

That will work fine and you won't get an error as you never try and add a value into the dictionary that already exists.

However, immediately following the line above, you try and add another value to the dictionary with:

dic.Add r.Offset(, 6).Value, Nothing

But you don't first check that this value doesn't already exist in the Dictionary - you get the error because it is already in the Dictionary (ie the value has been added in previously).

If you did check for r.Offset(,6).Value's existence first, you could skip trying to add an already-existing value. This would mean, however, that your two arrays (m_price and m_serial) would be 'out of step'. Because I don't know exactly why you are doing this, I am not sure if this is important to you.

 

[nightheart]

Thank you very much for your help.It is now working after eliminating that line.

Thanks again.