Excel Sum with VBA

Adding a sum from one sheet is traditionally done with formula where you have an idea of the data which will be incorporated each period.  The summary sheet updates accordingly when the new data is imported into the worksheet.  I recently produced a journal and the output needed to be summarised at cost centre level.  The summary of the journal needed to be placed in a summary sheet and it was to be done on the fly.  This led me down a path where I got in bed with the Scripting Dictionary.  One of VBA's most powerful tools for isolating data.  I came up with the following procedure which takes the debits and credits and summarises them by cost centre.

Option Explicit

Sub JnlSummary() 'Excel VBA which Sums columns D and E in file
Dim r As Range
Dim rng As Range
Dim sd As Object
Dim sd1 As Object
Dim a As Variant
Dim a1 As Variant
Dim ar As Variant
Dim arr As Variant
Dim var As Variant
Dim var1 As Variant
Dim i As Long
Dim sh As Worksheet
Dim ws As Worksheet

On Error Resume Next
Set ws=Sheet1
Set sh=Sheet2
Set sd=Createobject("Scripting.Dictionary")
Set sd1=Createobject("Scripting.Dictionary")
sd.CompareMode=vbTextCompare
sd1.CompareMode=vbTextCompare


Set rng=ws.Range("C1", ws.Range("C65536").End(xlUp)) 'Change this line to suit
For Each r In rng
a=Trim(r.Value)
ar=r.Offset(, 1).Value 'Change this to suit
arr=r.Offset(, 2).Value 'Change this to suit
If a <> "" Then
If Not sd.Exists(a) Then
sd.Add a, ar
sd1.Add a, arr
Else
sd(a)=sd(a) + ar
sd1(a)=sd1(a) + arr
End If
End If
Next r
'Clear the range of the Journal Form
sh.UsedRange.Offset(1).ClearContents
a1=sd.Keys
var=sd.Items
var1=sd1.Items
For i=0 To sd.Count - 1
sh.Cells(i + 10, "B")=a1(i) 'Offset by 10 Rows
sh.Cells(i + 10, "C")=var(i) 'Offset by 10 Rows
sh.Cells(i + 10, "D")=var1(i) 'Offset by 10 Rows
Next i
End Sub

The above looks long winded but will run remarkably quickly on large datasets. In the attached file I have used some of the data from another model on the site not debits and credits. The Important lines to change above are the rng (Change this line to suit). This is the data which will be consolidated and summarised. Two columns are evaluated in the attached file it is Column D and E. These are both summed. This can be updated by changing the offset, the following line

ar=r.Offset(, 1).Value

says offset column C by 1 column (column D). This can be changed to suit your data. The attached Excel VBA file should help.