VBA Excel adding values to dictionary

Question

Alright, maybe I've just been looking at this for too long but I keep getting an expected expression error, which I believe is just a syntax issue but I'm not entirely sure. I'm looping through one sheet and adding unique values of one column as keys to the dictionary while adding the numbers that correspond to the keys together. For example, if I have:

A 2
B 3
B 4
A 5
C 6

I want the dictionary to look like:

A 7
B 7
C 6

Here's my code, any help is appreciated.

Sub Name()
    Dim rng As Range
    Dim x As Integer
    Dim ranga As String
    Dim dico As Dictionary
    Set dico = New Dictionary
    Dim var As Variant
    Dim lastrow As Integer
    With Worksheets("Sheet1")
        lastrow = Range("A" & .Rows.Count).End(xlUp).Row
        ranga = "C6" & ":" & "C" & CStr(lastrow)
        Set rng = Range(ranga)
        For Each var In rng.Cells
            If dico.Exists(var.Value) Then
                dico(var.Value) = dico(var.Value) + var.Offset(0, 4).Value
            Else
                dico.add var.Value, var.Offset(0, 4).Value
            End If
        Next var
    End With

    With Worksheets("Sheet2")
        Set rng = Range("A2")
        Dim i As Integer
        i = 0
        For Each var In dico.Keys
            rng.Offset(i).Value = var
            rng.Offset(i, 1).Value = dico(var)
        Next var
    End With

End Sub

quick question: do you really need VBA for this? It would be more efficient to actually use built in tools and then load the data to a dictionary if still needs to be in a dictionary — user2140173
– user2140173, Commented Aug 6, 2014 at 12:51
Yeah, the table is constantly changing so even if I have static functions on the second sheet they wouldn't include new values. — RonTheBear
– RonTheBear, Commented Aug 6, 2014 at 12:53

Frank · Accepted Answer · 2014-08-07 04:07:30Z

3

I am new to stackoverflow, so I am a but unsure of the appropriate etiquette, but here is a working solution.

Public Sub dict_counter()
    Dim counter As New Dictionary
    Dim key As Range: Set key = ThisWorkbook.Sheets("sheet1").Range("A1")
    While Not IsEmpty(key)
        If counter.Exists(key.Value) Then
            counter(key.Value) = counter(key.Value) + key.Offset(ColumnOffset:=1)
        Else
            counter(key.Value) = key.Offset(ColumnOffset:=1)
        End If
        Set key = key.Offset(RowOffset:=1)
    Wend

    'Obviously you can output the dict contents to whatever location
    'is convenient

    Dim k As Variant
    For Each k In counter
        Debug.Print k; counter(k)
    Next k
End Sub

answered Aug 7, 2014 at 4:07

Frank

4532 silver badges10 bronze badges

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

Comments

neelsg · Accepted Answer · 2014-08-06 13:05:21Z

2

Instead of

dico(var.Value) = dico(var.Value) + var.Offset(0, 4).Value

It should be

dico.Item(var.Value) = dico(var.Value) + var.Offset(0, 4).Value

See MSDN

Also, if you use With, you have to actually put leading .'s where you want to use methods or properties of it like this:

With Worksheets("Sheet1")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

See MSDN

edited Aug 6, 2014 at 13:05

answered Aug 6, 2014 at 12:53

neelsg

4,8425 gold badges37 silver badges58 bronze badges

4 Comments

RonTheBear Over a year ago

hmmm. Maybe that was part of the problem, but I still get the same error.

neelsg Over a year ago

At what line does the code break and what is the exact error message you are getting?

RonTheBear Over a year ago

It doesn't give me a line oddly enough, it just says compile error, expected: expression

RonTheBear Over a year ago

I figured out that it was due to calling the sub "name".

Collectives™ on Stack Overflow

VBA Excel adding values to dictionary

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related