VBA Help Setting Up an Accumulator

[jessbrooks79]

Hello Everyone,
I am trying to set up an accumulator to keep track of many sub-accounts. I have Excel 2007 with Windows XP. I have been warned away from accumulators but I think it will be a good temporary fix until I can create a more reliable worksheet!
Below is my workbook. I have my sub-accounts set up in column C, Beginning Budget in D, E and F are any Deposits and Withdrawls that occur. In G I would like to calculate any amounts input in E and F.
This is the list of cells I need an accumulator in:
G26:G59, K26:K59, O26:O59, S26:S59,
G67:G107,K67:K107, O67:O107, S67:S107
G116:G145, K116:K145, O116:O145, S116:S145


Excel 2007
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #3b4e87; COLOR: #ffffff; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid"> SPECIAL EDUCATION PROGRAM</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #3b4e87; COLOR: #ffffff">Beginning Budget </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #3b4e87; COLOR: #ffffff">Deposit </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #3b4e87; COLOR: #ffffff">Withdrawl</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #3b4e87; COLOR: #ffffff; BORDER-RIGHT: black 1px solid">Account Balance</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">44</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">310-Contracts With Other Public Agencies</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaeaea">259,059.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea; BORDER-RIGHT: black 1px solid">259,059.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">45</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">311-Contracts With Other School Systems</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaeaea">- </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea; BORDER-RIGHT: black 1px solid">- </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">46</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">312-Contracts With Private Agencies</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaeaea">- </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea; BORDER-RIGHT: black 1px solid">- </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">47</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">322-Evaluation & Testing</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaeaea">- </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea; BORDER-RIGHT: black 1px solid">- </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">48</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">330-Operating Lease Payments</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #eaeaea">- </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea">0.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #eaeaea; BORDER-RIGHT: black 1px solid">- </TD></TR></TBODY></TABLE>

Report​

So far I have tried this:
I did some research on accumulators and found a code. I went into the developer tab on Excel and then clicked View Code. Under General I pasted the code I found.

Private Sub Worksheet_Report(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "G26" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("E26:F26").Value = Range("E26:F26").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub


I then tried inputting different amounts into my spreadsheet and the code had not worked. So I must be missing a step or more than likely,I messed the code up when I tried to enter my ranges.
I'm obviously very new to this and any guidance would be greatly appriceated!
Thanks in advance!

 

[jessbrooks79]

Okay, I got the below code to work for the cell range E26:E59

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static dAccumulator As Double
With Target
If Not Intersect(.Cells, Range("E26:E59")) Is Nothing Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dAccumulator = dAccumulator + .Value
Else
dAccumulator = 0
End If
Application.EnableEvents = False
.Value = dAccumulator
Application.EnableEvents = True
End If
End With
End Sub


Now I need to get a code that will allow me to add the accumulator to these ranges:

E26:E59
E67:E107
E116:E145
F26:F59
F67:F107
F116:F145
I26:I59
I67:I107
I116:I145
J26:J59
J67:J107
J116:J145
M26:M59
M67:M107
M116:M145
N26:N59
N67:N107
N116:N145
Q26:Q59
Q67:Q107
Q116:Q145
R26:R59
R67:R107
R116:R145

Is this possible?

Thanks again!