Type Mismatch on Dictionary Elements

[Ctsmiths192]

I cannot seem to figure out why I am having a Type Mismatch error on the adding part of the code. I am only able to add the first instance of a date change. Column A is a series of dates and Times and Columns B & C are values for those dates. The idea is to output the totals by date change....


Sub SummarizeData()
'Declare variables
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim dict As Object
Dim key As Variant
Dim dateVal As Date
Dim gallonVal As Double
Dim btuVal As Double

'Set worksheet object
Set ws = ActiveSheet

'Get last row of data
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

'Create a dictionary to store the date and values
Set dict = CreateObject("Scripting.Dictionary")

'Loop through the data rows
For i = 2 To lastRow
'Get the date value from column A
dateVal = Int(ws.Cells(i, "A").Value)

'Get the gallon value from column B
gallonVal = ws.Cells(i, "B").Value

'Get the BTU value from column E
btuVal = ws.Cells(i, "E").Value

'Check if the date already exists in the dictionary
If dict.Exists(dateVal) Then
'Add the gallon and BTU values to the existing ones
dict(dateVal)(1) = dict(dateVal)(1) + gallonVal
dict(dateVal)(2) = dict(dateVal)(2) + btuVal
Else
'Create a new entry in the dictionary with an array of values
dict(dateVal) = Array(dateVal, gallonVal, btuVal)
End If
Next i

'Output the summary table in columns G and H
ws.Range("G1").Value = "Date"
ws.Range("H1").Value = "Total Gallons"
ws.Range("I1").Value = "Total BTUs"
i = 2
For Each key In dict.Keys
'Write the date and values from the dictionary
ws.Range("G" & i).Value = dict(key)(0)
ws.Range("H" & i).Value = dict(key)(1)
ws.Range("I" & i).Value = dict(key)(2)
i = i + 1
Next key
End Sub

 

[Domenic]

First, try running your macro again. This time, though, when the error occurs, click on Debug. It should then take you to the line causing the error. What values are assigned to your variables on that line?

Secondly, you won't be able to add your values the way you're doing it. You'll need to first assign your array to a temporary variable, then do the addition using the temporary variable, and then add it back to your dictionary...

Dim tempArray As Variant

'Check if the date already exists in the dictionary
If dict.Exists(dateVal) Then
        'Add the gallon and BTU values to the existing ones
        tempArray = dict(dateVal)
        tempArray(1) = tempArray(1) + gallonVal
        tempArray(2) = tempArray(2) + btuVal
        dict(dateVal) = tempArray
    Else
        'Create a new entry in the dictionary with an array of values
        dict(dateVal) = Array(dateVal, gallonVal, btuVal)
    End If
Next i

Hope this helps!

 

[Ctsmiths192]

First, try running your macro again. This time, though, when the error occurs, click on Debug. What values are assigned to your variables?

Secondly, you won't be able to add your values the way you're doing it. You'll need to first assign your array to a temporary variable, then do the addition using the temporary variable, and then add it back to your dictionary...

Dim tempArray As Variant

'Check if the date already exists in the dictionary
If dict.Exists(dateVal) Then
        'Add the gallon and BTU values to the existing ones
        tempArray = dict(dateVal)
        tempArray(1) = tempArray(1) + gallonVal
        tempArray(2) = tempArray(2) + btuVal
        dict(dateVal) = tempArray
    Else
        'Create a new entry in the dictionary with an array of values
        dict(dateVal) = Array(dateVal, gallonVal, btuVal)
    End If
Next i

Hope this helps!

Yes Thanks! That makes sense...