|
Anthony's VBA Forum Where the knowledge is shared |
|
|
View previous topic :: View next topic |
Author |
Message |
grizzly
Joined: 05 Feb 2008 Posts: 5
|
Posted: Wed Feb 27, 2008 10:17 am Post subject: run total in excel |
|
|
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 |
|
|
mohan.ankur Moderator
Joined: 02 Aug 2006 Posts: 52
|
Posted: Thu Feb 28, 2008 12:07 pm Post subject: Running Total |
|
|
You mean running total in rows? |
|
Back to top |
|
|
grizzly
Joined: 05 Feb 2008 Posts: 5
|
Posted: Thu Feb 28, 2008 12:46 pm Post subject: Require several lines |
|
|
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 |
|
|
mohan.ankur Moderator
Joined: 02 Aug 2006 Posts: 52
|
Posted: Fri Feb 29, 2008 10:40 pm Post subject: Try This |
|
|
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 |
|
|
mohan.ankur Moderator
Joined: 02 Aug 2006 Posts: 52
|
|
Back to top |
|
|
grizzly
Joined: 05 Feb 2008 Posts: 5
|
Posted: Sat Mar 01, 2008 1:57 pm Post subject: |
|
|
thanks Anthony
works great |
|
Back to top |
|
|
grizzly
Joined: 05 Feb 2008 Posts: 5
|
Posted: Sun Mar 02, 2008 9:55 am Post subject: too much |
|
|
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 |
|
|
|
|
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
|