Great. Thanks Tom.


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:O0ykyT6sFHA.2592@TK2MSFTNGP09.phx.gbl...
> 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" <Johnsickofspam@aol.net> wrote in message
> news:%23KrIaQ6sFHA.2792@tk2msftngp13.phx.gbl...
>> 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" <twogilvy@msn.com> wrote in message
>> news:%23cBCnF6sFHA.1168@TK2MSFTNGP11.phx.gbl...
>> > 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" <Johnsickofspam@aol.net> wrote in message
>> > news:%23$Slf15sFHA.1132@TK2MSFTNGP10.phx.gbl...
>> >> 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!
>> >>
>> >>
>> >
>> >
>>
>>
>
>