-
Solved: key already associated with element of collection
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
-
If you use a structure like this[VBA]On 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[/VBA]
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules