Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Unanswered: trying to create run totals in excel

    newbee looking for help

    in colum "A" employees names. - in colum "E" & "F" overtime hours asked to work. - I want colum "B" to keep a run total of all hours. example - if A2 is at 8 hrs and I put 4 in E2 then A2 will change to 12 and if I put 8 in F2 A2 will change to 20
    I used this formula below using macros but will only do one line can I use it in multipl lines ---- line 1, line 2, line 3 etc.

    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]
    Last edited by grizzly; 01-23-08 at 06:41.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy and welcome to the board. You would need to add a counter for the rows and then loop through the rows. Here is one way to approach.

    Code:
    Sub test()
        Dim lngLastRow As Long
        Dim lngCounter As Long
        lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        For lngCounter = 2 To lngLastRow
            Cells(lngCounter, 2).Formula = Cells(lngCounter, 1) + Cell(lngCounter, 2)
        Next lngCounter
    End Sub
    You can either set this up as a normal module that you associate with a button, or you could call it from a Private module that will run automatically. You want to be careful doing it this second way because you want to avoid an endless loop.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2008
    Posts
    3
    sorry Shades I'm probably doing somthing wrong but I get a compile error

  4. #4
    Join Date
    Jan 2008
    Posts
    3
    need a run total in several lines

    A B C D E
    1 BILL 12 6 6
    2 TED 8 0 8
    3 ALICE 16 8 8
    4 SAM 6 6 0

    What I’m trying to do is keep a run total in column "B" from column "D" and column "E"
    : if D1 is changed to 8 the run total in column B1 will now be 20 (12+8=20)and if E1 is now changed to 8 then B1 will be 28 (20+8=28) etc. , etc.

    _below is the formula I used but will only work for one line

    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
    Please help if you can
    Last edited by grizzly; 02-05-08 at 17:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •