Great. Thanks Tom.


"Tom Ogilvy" wrote in message
...
because if I try to access a member of a collection and that member
doesn't
exist, then it raises an error. That is how I know the member doesn't
exist and can take the appopriate action.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hello Tom,

Thanks very much for this. It works perfectly, although I need to study
it
a bit longer to understand all the steps.

One questions though - why is the error handling necessary?

Anyway, great solution.

Thanks again

John


"Tom Ogilvy" wrote in message
...
Sub RemoveDuplicates()
Dim Rng As Range, Cell As Range
Dim List As New Collection
Dim v As Variant
Dim item As Variant

' The items are in A1:A10
Set Rng = Range("A1:A10")

On Error Resume Next
For Each Cell In Rng
v = List.item(Cell.Text)
Debug.Print Cell.Address, Err.Number
If Err.Number < 0 Then
v = Array(Cell.Text, Cell.Address(0, 0))
List.Add v, CStr(Cell.Value)
Else
v(1) = v(1) & "," & Cell.Address(0, 0)
List.Remove Cell.Text
List.Add v, CStr(Cell.Value)
End If
Err.Clear
Next Cell

' Resume normal error handling
On Error GoTo 0


' Print out the list is the Immediate window
For Each item In List
v = item
Debug.Print v(0), v(1)
Next item


End Sub

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi there,

I'm trying to get my head around multi-dimensional collections.

What I'm trying to do is to run through a column of data and check if
each
cell value is "correct" (through some other logic). If it is not
correct
then I want to add the value (string) to a collection and its
associated
cell reference.
I'm assuming thus far that I need a 2 dimensional collection?

Now, if further down the column I come across the same incorrect
string,
I
want to find the existing collection item and add another cell
reference
to
it.

So (where "Bob" and "Mary" are NOT "correct"):

Bob
Mary
Susan
Bob

Would therefore be added to the collection as:

Item 1 ("Bob", "A1")
Item 2 ("Mary", "A2")
Item 1 ("Bob", "A1" & "A2")

Can anyone help me get this sorted out?

Thanks in advance

John

PS - The reason that I'm trying to use a collection rather than an
array
is
that I'm assuming that it's easier to reference the elements by name
(string)? I happy to be corrected!