Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    How to Autosum in a macro at each change in data

    Hi All,

    I'm looking for some code that will add a 2 lines as an offfset and then auto sum the amounts column at each change in data in columns A.

    I cant seem to athach a workbook due to my companys internet. Any help would be much appreciated.

    TYPE ACCOUNT NUMBER ACCOUNT NAME LONDON GROSS LONDON OPEN
    Sale Totals 1234 Test 100.00 100.00
    Sale Totals 1234 Test 300.00 300.00
    Sale Totals 1234 Test 400.00 400.00
    Purchase Totals 1234 Test -200.00 -200.00
    Purchase Totals 1234 Test -100.00 -100.00


    I.e. I would like 3 lines inserted after sale totals and then in line 2 the sum of London and London Gross etc. The data will change everyday so a fixed range wont work.

    Any help is appreciated.

    Regards,

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Have you tried using the built-in subtotal function? Once you have the subtotals added, you can collapse the data, select it, press Alt+; to select just the visible cells and then use Insert-Rows to add the blank rows.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts

    Autosum and advance rows

    Stimp,

    Not sure if this is what you are looking to do but here is a table as you describe with 3 rows under Sales Totals. On the second of those 3 rows is Totals which adjacent to it is the totals of London Gross and London Open. Below is code that if you entered a new Type in Cell A5, everything would drop below it by inserting a new line while keeping the running totals. It works off of the Worksheet_Change event so it will occur on entry into next available cell in column 1

    HTH
    Maud

    Drop1.jpg Drop2.jpg

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = Range("A1").End(xlDown).Row And Target.Column = 1 Then
        lLastrow = Range("A1").End(xlDown).Row + 1
        Rows(lLastrow).Select
        Selection.Insert
        ActiveCell.Offset(-1, 1).Select
        Cells(lLastrow + 1, 4).Value = WorksheetFunction.Sum(Range(Cells(2, 4), Cells(lLastrow - 1, 4)))
        Cells(lLastrow + 1, 5).Value = WorksheetFunction.Sum(Range(Cells(2, 5), Cells(lLastrow - 1, 5)))
    End If
    End Sub

Posting Permissions

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