Anthony's VBA Forum Index Anthony's VBA Forum
Where the knowledge is shared
Menu
 Anthony's VBA Forum IndexHome Page
 Anthony's VBA Forum IndexForum Index
FAQFAQ
MemberlistMemberlist
UsergroupsUsergroups
RegisterRegister
ProfileProfile
Log in to check your private messagesMessages
Log inLogin/Out

Quick Search

Advanced Search

Links
Consulting
Products

Who's Online
[ Administrator ]
[ Moderator ]


run total in excel

 
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA
View previous topic :: View next topic  
Author Message
grizzly



Joined: 05 Feb 2008
Posts: 5

PostPosted: Wed Feb 27, 2008 10:17 am    Post subject: run total in excel Reply with quote

Is it posible to create a run total in excel in several lines on one spread sheet. I used the formula below but will only do one line
Thanks
_____________________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("A2")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Add A2's value to B2
[B2] = [B2] + [A2]

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
Back to top
View user's profile Send private message
mohan.ankur
Moderator
Moderator


Joined: 02 Aug 2006
Posts: 52

PostPosted: Thu Feb 28, 2008 12:07 pm    Post subject: Running Total Reply with quote

You mean running total in rows?
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
grizzly



Joined: 05 Feb 2008
Posts: 5

PostPosted: Thu Feb 28, 2008 12:46 pm    Post subject: Require several lines Reply with quote

yes
I want to keep a run total in line 1
I also want to keep a run total in line 2
line 3 etc.

but ( for example ) 1f I put 6 in E1 and 6 in F1 then A1=12 but if I then change F1 to 7 then A1 now = 19 and if I change E1 to 10 now A1 = 29 etc. etc.
the formula I'm using works. but for one line only. I can change it to work in different lines but still only one line at a time and I need several lines
Thanks
__________________________________________________________________________________________________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("A2")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Add A2's value to B2
[B2] = [B2] + [A2]

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
Back to top
View user's profile Send private message
mohan.ankur
Moderator
Moderator


Joined: 02 Aug 2006
Posts: 52

PostPosted: Fri Feb 29, 2008 10:40 pm    Post subject: Try This Reply with quote

This will work for all rows and will keep the running totals in column B.

------

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Target
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Add A2's value to B2
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + Target.Value
Application.EnableEvents = True
End Sub
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
mohan.ankur
Moderator
Moderator


Joined: 02 Aug 2006
Posts: 52

PostPosted: Fri Feb 29, 2008 10:46 pm    Post subject: Did You get it right? Reply with quote

Let me know if you get it right?

Thanks
_________________
http://www.exceldepot.com
http://www.xlmacros.com
http://www.learnexcel.org
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
grizzly



Joined: 05 Feb 2008
Posts: 5

PostPosted: Sat Mar 01, 2008 1:57 pm    Post subject: Reply with quote

thanks Anthony
works great
Back to top
View user's profile Send private message
grizzly



Joined: 05 Feb 2008
Posts: 5

PostPosted: Sun Mar 02, 2008 9:55 am    Post subject: too much Reply with quote

the formula works too well - it changes more than I require
C D E F G H I
1 John 8 8 16
2 Paul 4 4 8
3 Ted 8 0 8
4 Alice 0 4 4

Note: John has worked 8 hrs. in C and 8 hrs. in D -- total = 16 hrs. in F
Paul has worked 4 hrs. in C and 4 hrs. in D --- total = 8 hrs in F
Ted has worked 8 hrs in C and 0 hrs in D---- total = 8 hrs. in F
Alice has worked 0 hrs. in C and 4 hrs. in D -- total = 4 hrs. in F
I would like to change John's C to a 10 and total in F is changed to 26 hrs and then change John's D to 4 and total in F is automatically changed to 30 hrs. ----- and so on for each person
Hope you can help
[/list]
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group