1

UPDATED with questions still: So I've used count before and haven't had issue. However I am trying to get a total for each unique value in a spreadsheet. I'm using vb script because I need this to be outside of excel for several reasons. So if my data is like :

Bob
Bob
Ted
Ann
Ann

I'm looking for the results to be 
Bob =2
Ted = 1
Ann = 2
etc...

This is what I have so far, which gets me my unique count but not a total for the unique items...

    Dim objDict,item,arr,cRow, result,count
        Set objDict = CreateObject("Scripting.Dictionary")
        arr= .Sheets(1).Range("A2:A" & iLastRow )
    For Each key In arr
           If key <>"" Then
              If Not objDict.Exists(key) Then objDict.Add key, Nothing
              else
                 objDict.key("name").Item = objDict.Item() + 1 
              end if
            End If
    Next

I've updated with Marks suggestion, yet I continue to get hung up on key. I'm not understanding how to use in this situation. From the help file Mark supplied, the example is adding a key and an item. I'm just adding keys, so what am I missing here?

Thanks for any help in this. Just pointers in the right direction are most welcome.

okmred
  • 147
  • 2
  • 12
  • You need an `else` line so if it exists you read the item, add 1 to it, then update the item. Note you are calling the `key` an item which must be confusing to you as dictionaries are `Key, Item`. –  May 06 '20 at 22:15
  • So add something like: Else count(item)+1 – okmred May 06 '20 at 22:31
  • 2
    `objDict.Key("name").Item = objDict.Item() + 1` –  May 06 '20 at 22:48
  • ok, i'll play with that a bit. Thanks!! – okmred May 06 '20 at 23:02
  • Also see: https://excelmacromastery.com/vba-dictionary/ – Rno May 06 '20 at 23:13
  • Mark, what is name? objDict.Key("name").Item = objDict.Item() + 1 I'm getting Object doesn't support "Item" – okmred May 06 '20 at 23:41
  • 1
    Here is the help file. Dictionary is under Scripting Runtime. http://download.microsoft.com/download/winscript56/Install/5.6/W982KMeXP/EN-US/scrdoc56en.exe –  May 06 '20 at 23:51
  • Does this answer your question? [How to get unique values from a list of values using VBscript?](https://stackoverflow.com/questions/13743165/how-to-get-unique-values-from-a-list-of-values-using-vbscript) – user692942 May 11 '20 at 12:55
  • The code I posted gets me the unique values, I was just stuck on the count. Thanks. – okmred May 19 '20 at 18:05
  • Is there a way to give Mark points as his help put me in the right direction. How else to close the question? Thanks – okmred Jul 15 '20 at 16:50

0 Answers0