(Solved) – vba dictionary when I transpose the dictionary Items, I get all zeros


The first thing I do is create a scripting dictionary setting the Keys from each row in a column and the number zero as the item. In subsequent code, when conditions are met, I pull a Key’s Item as an integer, add 1 to it and replace the 0 with a 1 in the dictionary.

For testing the code, when I call the Key specifically I get a 1 for the Item as intended. When I transpose the Items of that dictionary into a column, it gives me all zeros. When I set it up as iterating through a For Loop, placing the Items into subsequent rows within a column, I still get all zeros.

I’m not worried about maximums, there are only like 40 recruiters I’m checking.


LRR is a last row variable

'This creates the dictionary. Recruiter Key with a 0 item
Set RecGoalCount = CreateObject("scripting.dictionary")
    For Each RecDesk In Sheets("Recruiters").Range("B2:B" & LRR)

        RecGoalCount.Add RecDesk, 0

    Next RecDesk
Sheets("Recruiters").Range("E2:E" & LRR) = Application.Transpose(RecGoalCount.Items)
      'This is where I get all 0s despite a condition in the code changing exactly 1 Item to a 1
Sheets("Recruiters").Range("F2:F" & LRR) = Application.Transpose(RecGoalCount.Keys)
      'This is just proving to me that it captured the correct keys with the correct names, check.
Sheets("Recruiters").Range("H2") = RecGoalCount("14CBBPG")
      'This proves to me that the Item became a 1
Recs = RecGoalCount.Count
      'This proves to me that after the code that changes the item, the total _
       count of the dictionary didn't change either.
'This code iterates through the dictionary keys to give the items, and it _
    gives me all 0s despite one of the items being turned into a 1
x = 2
    For Each Rec In RecGoalCount

        Sheets("Recruiters").Range("E" & x) = RecGoalCount(Rec)
        x = x   1

    Next Rec

Leave a Reply

Your email address will not be published. Required fields are marked *