0

What I am trying to accomplish

I'd like to take a series of values from a selection of cells, then fill a new cell which contains only unique values from the selection I made. here is my code so far:

Const Delimiter = ", "
Dim num As Range
Dim a As Variant
Dim Concat, bucket As New Collection

#to create a collection that contains all the values from my selection 
For Each num In Selection
  a = Split(num, Delimiter)
  Concat.Add (a)
Next num

#to convert multidimensional collection to a single dimensional
For i = 1 To Concat.Count
  For j = 1 To Concat(i).Count
    bucket.add(Concat(i)(j))
  Next i
Next j

#to remove duplicate values
[code]

#to output to excel
[code]

As you can see, the code is incomplete. I am having issues with the following lines of code

For j = 1 To Concat(i).Count

I get a "Run-time error '424': Object required" error.

user23960
  • 1
  • 1
  • 3
  • 2
    `Concat(i)` is an array, not a collection, so you'll want to use `UBound` instead of `.Count` to iterate over it (note that the array returned by `Split()` will be 0-indexed so your loop should start at `i=0`). Also, `Dim Concat, bucket As New Collection` should cause you even more errors, because it makes `Concat` an empty Variant while `bucket` becomes the Collection you allocated. You need to individually `Dim` each variable like `Dim Concat As New Collection, bucket As New Collection` – Mikegrann Sep 06 '16 at 18:18
  • I tried UBound and I am still getting the same error message – user23960 Sep 06 '16 at 18:28
  • 5
    Use a [Dictionary](http://stackoverflow.com/documentation/vba/3667/scripting-dictionary-object) instead of a Collection. That way you can test to see if it's a duplicate *before adding it*. – Comintern Sep 06 '16 at 18:29
  • BTW `Dim foo, bar As Whatever` only declares a type to the last variable in the list (e.g. `bar As Whatever`), leaving everything else an implicit `Variant` (e.g. `Dim foo`) - so `bucket` is a `Collection` and `Concat` is a `Variant`. And VBA comments are made using a single quote. And your use of extraneous parentheses will eventually make you run into weird runtime error 424 "Object required" ...oh, wait. – Mathieu Guindon Sep 06 '16 at 18:40
  • @Mat'sMug # instead of ' is for the purpose of clarity. the runtime error is in regards to the line w/o the parenthesis – user23960 Sep 06 '16 at 18:49
  • @Comintern can you elaborate further, please? – user23960 Sep 06 '16 at 18:54
  • 1
    See the answer below. I was going to post one similar to it. BTW, "for the purpose of clarity" it wouldn't hurt to get in the habit of explicitly calling default members (i.e. `Concat.Items(a)` instead of `Concat(a)`). That would give you an instant hint that something was wrong with your declaration because the IntelliSense wouldn't have popped up after the `.`. – Comintern Sep 06 '16 at 18:57
  • @Comintern thanks for the pointers, I will keep that in mind moving forward. Good day :) – user23960 Sep 06 '16 at 19:27

1 Answers1

3

Using a dictionary will make it a lot simpler and easier. See below.

Sub UniqueValues()
    Const Delimiter = ","
    Dim num As Range
    Dim a As Variant
    Dim i As Integer, j As Integer
    Dim dctData As New Dictionary

    'Loop through all the values in the cells (including splitting into arrays)
    For Each num In Selection
        a = Split(num, Delimiter)
        For j = 0 To UBound(a, 1)
            If Not dctData.Exists(Trim(a(j))) Then
                dctData.Add Trim(a(j)), ""
            End If
        Next j
    Next num

    '#to output to excel
    For Each a In dctData
        Debug.Print a
    Next a
End Sub
Coolshaikh
  • 146
  • 4