1

Basically, I want my code to go down through the values in a range and add to a dictionary only unique values. That is, only add the nth value if it has not been previously added. The result being a dictionary which keys are all the values from the range without duplicates. Here's my code:

Sub CreatePatientList()

    Dim outputsh As Worksheet
    Set outputsh = Workbooks(1).Sheets(1)
    Dim recDict As New Scripting.Dictionary

    Dim i As Variant, rowCount As Long
    rowCount = outputsh.Cells(Rows.Count, 1).End(xlUp).Row
    For Each i In Range("O2:O" & rowCount)
        If recDict.Exists(i) Then
            MsgBox ("Exists")
        Else
            recDict.Add i, i
        End If
    Next

    Set recDict = Nothing

End Sub

Right now, 100% of the values in the range are being added to the dictionary.

Any help? Thanks in advance!!

Carlos Andres
  • 69
  • 1
  • 8

1 Answers1

2

Dim i As Variant is doubly confusing:

  • i is normally a counter, e.g. a Long, in a For...Next loop.
  • If you're looping through a Range with a For Each...Next loop, then use a Range for your element variable.

So Dim cell As Range, for example.

Then, you need to be explicit about the .Value of the cell for the key. Currently the range is the key, not its value, and since each range is unique, they are all added to the dictionary.

Dim cell As Range, rowCount As Long
...
For Each cell In Range("O2:O" & rowCount)
    If recDict.Exists(cell.Value) Then
        MsgBox "Exists"
    Else
        recDict.Add Key:=cell.Value, Value:=cell.Value
    End If
Next

Note that if you just want the unique values (keys), you don't have to check Exists, as demonstrated here. Also demonstrated in the same answer, it'll be faster to load the data to a Variant array first, and then read the array into the dictionary.

BigBen
  • 46,229
  • 7
  • 24
  • 40