Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add totals for each date
how can i create a macro that goes to column "A", insert a row after a date,
goes to column "C", get a total for that date in column "D" of the same row, and continue the same procedure. lets say: column a b c d row 1 09/01/2006 amex 1000 row 2 09/01/2006 visa 400 row 3 09/01/2006 visa 800 09/02/2006 visa 500 09/02/2006 amex 200 result should be: column a b c d row 1 09/01/2006 amex 1000 row 2 09/01/2006 visa 400 row 3 09/01/2006 visa 800 2200 09/02/2006 visa 500 09/02/2006 amex 200 700 and so on, thanks a lot |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add totals for each date
This will work if you have no header row, as indicated by your data:
Sub InsertTotals() Dim LastRow As Long Dim StartRow As Long Dim EndRow As Long Application.ScreenUpdating = False LastRow = Range("A65536").End(xlUp).Row For i = LastRow To 2 Step -1 If Range("A" & i).Value < _ Range("A" & i - 1).Value Then Range("A" & i).EntireRow.Insert End If Next LastRow = Range("A65536").End(xlUp).Row For i = 1 To LastRow + 1 If Range("A" & i).Value < "" Then If StartRow = 0 Then StartRow = i End If Else EndRow = i - 1 Range("D" & i).Formula = _ "=SUM(C" & StartRow & ":C" & EndRow & ")" StartRow = 0 End If Next Application.ScreenUpdating = True End Sub And this will work if you do: Sub InsertTotals() Dim LastRow As Long Dim StartRow As Long Dim EndRow As Long Application.ScreenUpdating = False LastRow = Range("A65536").End(xlUp).Row For i = LastRow To 3 Step -1 If Range("A" & i).Value < _ Range("A" & i - 1).Value Then Range("A" & i).EntireRow.Insert End If Next LastRow = Range("A65536").End(xlUp).Row For i = 2 To LastRow + 1 If Range("A" & i).Value < "" Then If StartRow = 0 Then StartRow = i End If Else EndRow = i - 1 Range("D" & i).Formula = _ "=SUM(C" & StartRow & ":C" & EndRow & ")" StartRow = 0 End If Next Application.ScreenUpdating = True End Sub Regards Trevor "andresg1975" wrote in message ... how can i create a macro that goes to column "A", insert a row after a date, goes to column "C", get a total for that date in column "D" of the same row, and continue the same procedure. lets say: column a b c d row 1 09/01/2006 amex 1000 row 2 09/01/2006 visa 400 row 3 09/01/2006 visa 800 09/02/2006 visa 500 09/02/2006 amex 200 result should be: column a b c d row 1 09/01/2006 amex 1000 row 2 09/01/2006 visa 400 row 3 09/01/2006 visa 800 2200 09/02/2006 visa 500 09/02/2006 amex 200 700 and so on, thanks a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
sum year to date totals for particular month | Excel Worksheet Functions | |||
Year to date totals | Excel Discussion (Misc queries) | |||
Totals calculated by date | Excel Discussion (Misc queries) | |||
Sorting by date, then calculating totals | Excel Programming |