• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running Balance/total using Excel VBA

V

vicmoment

Member
What I want to do is perform a running balance for data in column a. An example is when i have data in columnA, i want the balances to show in column B using VBA

For example,


ColA ColB

1 1

4 5 (1+4)

5 10 (1+4+5)

6 16 (1+4+5+6)

7 23

9 32


I have written a small code like this but am new to VBA.


*****************************************************************************************

Sub runbalance()

Dim wksht As Worksheet

Dim counter As Long


Set wksht = ActiveSheet


For counter = 1 To 10


wksht.Cells(counter, 1).Offset(0, 1).Value = Application.WorksheetFunction.Sum(wksht.Range("cells(1,1):cells(counter,1)")).Value


Next counter


End Sub

******************************************************************************************


But it's not working. Please help
 
Hi ,


The following will work. A few minor changes , especially in the Range(Cells.... ) syntax.


Sub runbalance()

Dim wksht As Worksheet

Dim counter As Long


Set wksht = ThisWorkbook.ActiveSheet


For counter = 1 To 10


wksht.Cells(counter, 1).Offset(0, 1).Value = Application.WorksheetFunction.Sum(wksht.Range(Cells(1, 1), Cells(counter, 1)).Value)


Next counter


End Sub


Narayan
 
Hi,


Just a thought..


If you really dont need to VBA for this, you can still go for formula like (left cell of this row) + (above cell of this row)


I mean for B1 it will be just =A1

while for B2 and down it shall be =A2+B1, thats it. Just drag down till your end.


Regards,

Prasad

PS: I apologies if my solution is found very silly..
 
Not at all


Your solution is the way to go.


Just that in some other application we use in my office, we run scripts to perform operations like running balances and i wanted to see how that could be done with VBA although there's another twist to my question which i'ld post shortly.
 
See the spreadsheet in the link below.


I want to do a running balance for each account.


When the account changes, the sum is reset and continues to sum up till it gets to the next change in account.


the link is:

https://docs.google.com/spreadsheet/ccc?key=0An2orugVlNhDdFpzRkxRTVk4bVV2LWs3Z01obmZNVHc
 
Vicmoment


1. Your Data

In Your Example D15 the value is wrong, it should be 143, and the values below D15 are also wrong


2. The solution

D2: =C2

D3: =IF(A3=A2,E2+C3,C3)

Copy D3 down


3. I would not recommend using VBA for this application, and would only suggest it if the data has been exported from some other package

I Would then only use VBA to setup the formula besides the new data and not actually do the work

Just my 5 cents worth.
 
You ate definitely right about the value in D15, pasted in ahurry.


I agree with your suggestion on not using VBA, i only wanted to see how it could be done with it. Are you familiar with ACL(Audit command language). Performing a similar task must be done with a script, so i only wanted to see how VBA/Excel compares.


Thanks Hui
 
You must log in or register to reply here.
Back
Top