PDA

View Full Version : Solved: key already associated with element of collection



philfer
01-25-2008, 12:57 PM
Hello

I am using the code below to extract a list of unique items :-

Dim uniqueitem As Collection
Dim cell As Range

Set uniqueitem = New Collection

For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
uniqueitem.Add Cstr(cell), Cstr(cell)
Next

The error I get is :=

Key is already associated with an element of this collection

I found the code on the web so just gave it a go.

Also the person that posted the code said that you cannot have duplicate items in a collection but I found that taking out the second Cstr(cell) loops down the column and picks up each item even duplicates

Can anyone help

mikerickson
01-25-2008, 01:45 PM
If you use a structure like thisOn Error Resume Next
For each oneCell in Range("A1:A9000")
myCollection.Add item:=CStr(oneCell.Value), key:= CStr(oneCell.Value)
Next oneCell
On Error Goto 0
It will ignore the error a duplicate value causes, but doesn't add it to the collection, and carrys on with the next cell until it reaches the end of the range.