VBA dictionary not adding values

Question

I'm trying to write a function that searches a certain column (Call it Column A) for the term "Request". Once this row is found it searches another column (Call it Column B) in the same row for a unique term. The goal is to count how many unique terms in Column B correspond to the term "Request" in column A. The way I've tried to do this is by creating a dictionary and adding a term to it if that term does not already exist in the dictionary. The program counts how many terms are added to the dictionary so I know how many unique terms there are. However, the code I have now just keeps adding every term to the dictionary, regardless of whether or not the term already exists or not. I've been trying to figure it out for a while now and can't quite figure it out. Here's the code:

   j = 0
   For i = 1 To LastRow
        If Cells(i, 13).Value Like "Request" Then
            Set dic = CreateObject("Scripting.Dictionary")
            If Not dic.exists(Cells(i, 13)) Then
                ucount = ucount + 1
                dic.Add j, Cells(i, 13)
                j = j + 1
            End If
        End If
    Next i

I think the issue might lie with the dic.Add feature, but I'm not sure. I'm relatively new to VBA and coding in general.

Add parameters are key, value - the Exists method checks the key, not the value, and you're adding j as the key when you add an item. Also you have no wildcards in your Like comparison, so it will only find the exact value "Request" - maybe you meant "Request" ? — Tim Williams
– Tim Williams, Commented Jul 2, 2014 at 17:00
Good suggestion, but I actually had it the other way around before and it didn't work like that either. I mixed it around to see what would happen but obviously it didn't help. — user3783788
– user3783788, Commented Jul 2, 2014 at 17:03

Tim Williams · Accepted Answer · 2014-07-02 17:07:02Z

4

Dim dict as Object, tmp
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To LastRow
    If Cells(i, "A").Value = "Request" Then
        tmp = Cells(i, "B").Value
        If Not dict.exists(tmp) Then
            dict.Add tmp, 1
        End If
    End If
Next i 

Debug.Print dict.Count 'no need for a separate counter

answered Jul 2, 2014 at 17:07

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Zemens Over a year ago

+1 no need for separate counter variable. Also, unless the user needs to modify the stored value if the key already exists, it's safe to omit the If Not dict.exists test in favor of simply dict(tmp) = 1

user3783788 Over a year ago

Sorry I'm fairly new to this site and didn't think to do that.

enderland · Accepted Answer · 2014-07-02 17:35:31Z

1

Your problem is where this line is:

        Set dic = CreateObject("Scripting.Dictionary")

Each time you hit this line, your dictionary is reset and reinitialized.

Move it before your entire loop:

   j = 0
   Set dic = CreateObject("Scripting.Dictionary")
   For i = 1 To LastRow
        If Cells(i, 13).Value Like "Request" Then
            If Not dic.exists(Cells(i, 13).value) Then
                ucount = ucount + 1
                dic.Add j, Cells(i, 13).value
                j = j + 1
            End If
        End If
    Next i

edited Jul 2, 2014 at 17:35

answered Jul 2, 2014 at 17:06

enderland

14.3k18 gold badges105 silver badges157 bronze badges

2 Comments

user3783788 Over a year ago

I just tried it and unfortunately I still have the same issue. The code just keeps looping through the second if statement regardless of whether or not the term has been seen or not.

Patrick Lepelletier Over a year ago

you if not dic.exist must be the key value , being J !,

Collectives™ on Stack Overflow

VBA dictionary not adding values

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related