Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Running Column Total (Excel XP)

    My father who is elderly has trouble summing columns. Is there a way of creating a column where the running total is always displayed at the bottom and advances (moves down) every time you enter a new number ??? And Also to erase what you have done without erasing the cell that will act as a total so he can easily start over.
    The attached photo gives the running sum. the next number you enter would go into a4 and the total would move automatically into a6.Thanks Jerome

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Column Total (Excel XP)

    Add this macro to the worksheet object in VB of the sheet you want this to run on:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Dim rLastCell As Range
    Application.EnableEvents = False
    Set rLastCell = Range("A65536").End(xlUp)
    Range("A:A").Interior.ColorIndex = xlNone
    If rLastCell.Row <> 1 Then _
    rLastCell.Clear

    Set rLastCell = Range("A65536").End(xlUp)

    With rLastCell.Offset(2, 0)
    .Value = Application.WorksheetFunction.Sum(Range(Range("a1" ), rLastCell))
    .Interior.Color = vbYellow
    End With
    Application.EnableEvents = True
    Set rLastCell = Nothing
    End If
    End Sub</pre>


    An alternative (without a macro), but isn't what you asked for is to put the formula in A1
    <pre>=SUM(A2:A65536)</pre>


    and select A2
    window -freeze pane

    Then just enter into A2- end
    A1 will be the sum, format as desired. SInce you freeze the panes it will always be at the top and visible.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    Steve Many Thanks
    Can't seem to get it to work
    Have attached the excel file.
    Is it also possible to create say 3 columns of running totals and have the total on A2 vs A1 so I can give a1 a descriptive title
    Jerome

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Column Total (Excel XP)

    I assume you are going for the "non-macro" solution:
    Put the headers in Row 1 and use the formulas in row 2:
    <pre>=SUM(A3:A65536)</pre>


    You can copy this to other columns or modify the formulas as needed

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    Steve:
    Actually NO I am USING THE MACRO, I put it into sheet 1 see attached file same as last time, it has the visual basic in it that you wanted, but maybe it is named wrong

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Column Total (Excel XP)

    I don't see the macro in either of your attachments, which is why I assumed you went the other way. It needs to go into the "microsoft excel object" named sheet 1 in your example.

    It makes no difference to the macro what row you start on (unless it clears the interior color when you do not want it to, which could be modified).

    It just adds the sum of all the rows and puts it in the row 2 below the last item.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    Steve:
    Many thanks, I got it to work, when I pasted the code right from the email into the sheet there was a compile error, so had to paste it to wordpad and then back into the visual basic editor. When I did this some how got pasted into a wfxl2000.xls sheet instead of into the proper workbook.
    I tried to make it multicolumn so Dad could keep separate accounts per column but got a compile error when i did the following, Got hung up on the first line
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Dim rLastCell As Range
    Application.EnableEvents = False
    Set rLastCell = Range("A65536").End(xlUp)
    Range("A:A").Interior.ColorIndex = xlNone
    If rLastCell.Row <> 1 Then _
    rLastCell.Clear

    Set rLastCell = Range("A65536").End(xlUp)

    With rLastCell.Offset(2, 0)
    .Value = Application.WorksheetFunction.Sum(Range(Range("a1" ), rLastCell))
    .Interior.Color = vbYellow
    End With
    Application.EnableEvents = True
    Set rLastCell = Nothing
    End If
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Dim rLastCell As Range
    Application.EnableEvents = False
    Set rLastCell = Range("B65536").End(xlUp)
    Range("B:B").Interior.ColorIndex = xlNone
    If rLastCell.Row <> 1 Then _
    rLastCell.Clear

    Set rLastCell = Range("B65536").End(xlUp)

    With rLastCell.Offset(2, 0)
    .Value = Application.WorksheetFunction.Sum(Range(Range("b1" ), rLastCell))
    .Interior.Color = vbYellow
    End With
    Application.EnableEvents = True
    Set rLastCell = Nothing
    End If
    End Sub
    Thanks Jerome

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Column Total (Excel XP)

    Is this what you are after? It works on a& B change as appropriate.
    Or do you need all the subtotals to be on the same line (currently 2 rows from lowest one).

    Steve

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("A:B")
    If Not Intersect(Target, rng) Is Nothing Then
    Dim rInt As Range
    Dim rLastCell As Range
    Dim iCol As Integer
    Dim iFirstCol As Integer
    Dim iLastCol As Integer
    Dim lFirstRow As Long

    lFirstRow = 2
    Set rInt = Intersect(Target, rng)
    iFirstCol = rInt.Column
    iLastCol = iFirstCol + rInt.Columns.Count - 1


    Application.EnableEvents = False

    For iCol = iFirstCol To iLastCol
    Set rLastCell = Cells(65536, iCol).End(xlUp)
    Range(Cells(1, iCol), Cells(65536, iCol)).Interior.ColorIndex = xlNone

    If rLastCell.Row > lFirstRow Then _
    rLastCell.Clear

    Set rLastCell = Cells(65536, iCol).End(xlUp)

    With rLastCell.Offset(2, 0)
    .Value = Application.WorksheetFunction.Sum( _
    Range(Cells(lFirstRow, iCol), rLastCell))
    .Interior.Color = vbYellow
    End With
    Next
    Application.EnableEvents = True
    Set rLastCell = Nothing
    Set rInt = Nothing
    End If
    Set rng = Nothing
    End Sub</pre>


  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    Steve:
    Many Many Many Thanks. Absolutely Superb, my father loves it. Thanks for making things easier for us.
    Jerome

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Column Total (Excel XP)

    Minor modifications:

    Steve

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("A:B")
    If Not Intersect(Target, rng) Is Nothing Then
    Dim x As Integer
    Dim rInt As Range
    Dim rLastCell As Range
    Dim iCol As Integer
    Dim iFirstCol As Integer
    Dim iLastCol As Integer
    Dim lFirstRow As Long

    lFirstRow = 2
    Set rInt = Intersect(Target, rng)
    iFirstCol = rInt.Column
    iLastCol = iFirstCol + rInt.Columns.Count - 1


    Application.EnableEvents = False

    For iCol = iFirstCol To iLastCol
    Range(Cells(1, iCol), Cells(65536, iCol)).Interior.ColorIndex = xlNone

    For x = 1 To 2
    Set rLastCell = Cells(65536, iCol).End(xlUp)
    If rLastCell.Row > lFirstRow Then _
    rLastCell.Clear
    Next
    Set rLastCell = Cells(65536, iCol).End(xlUp)

    With rLastCell.Offset(2, 0)
    .Value = Application.WorksheetFunction.Sum( _
    Range(Cells(lFirstRow, iCol), rLastCell))
    .Interior.Color = vbYellow
    End With
    With rLastCell.Offset(3, 0)
    .Value = Application.WorksheetFunction.Count( _
    Range(Cells(lFirstRow, iCol), rLastCell)) & _
    " Items"
    .Interior.Color = vbRed
    End With

    Next
    Application.EnableEvents = True
    Set rLastCell = Nothing
    Set rInt = Nothing
    End If
    Set rng = Nothing
    End Sub</pre>


  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    Steve:
    Many thanks, just perfect
    Jerome

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    This has been great, Any chance of adding an item count at the bottom of each column say in red vs yellow. I know this sounds stupid, but Dad asked for it so he does not have to subtract the header and can verify the count. See attached jpg
    THanks you have been terrific
    Jerome

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    Steve:
    The running total is great. Is there an easy way to have all the numbers (Except for the count) to format automatically as $0.00 as dollar amounts with two decimals including the sum see attached photo (but i could not get the sum to show it)
    Many Thanks
    Jerome

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Column Total (Excel XP)

    Add this line to the code
    <pre>Range(Cells(1, iCol), Cells(65536, iCol)).NumberFormat = "$#,##0.00"</pre>


    Between the
    <pre>End with</pre>


    and the
    <pre>Next</pre>


    Steve

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Running Column Total (Excel XP)

    Steve:
    Many thanks, Lastly can i add an autoexec / autorunn maro that on opening the file asks if you want to erase all the entries ie start with a clean running total slate or keep current entries.???
    Thanks again, This is really great
    Jerome

Page 1 of 2 12 LastLast

Posting Permissions

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