PDA

View Full Version : summing months



lior03
06-08-2006, 02:31 PM
hello
the following macro takes a list of dates at one column and data on the second column and seprate every month.how can i use the sum function to
sum every month at each empty row i created
Application.ScreenUpdating = False
Dim iToday As Integer
Dim iYesterday As Integer
selection.CurrentRegion.Select
selection.numberformat = "dddd dd/mm/yyyy "
selection.Font.Bold = True
iYesterday = month(ActiveCell.Value)
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
iToday = month(ActiveCell.Value)
If iToday <> iYesterday Then
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If
iYesterday = iToday
Loop
Application.ScreenUpdating = True
thanks

geekgirlau
06-09-2006, 01:38 AM
Is there a reason you don't want to use either subtotals or pivot tables for this?

Bob Phillips
06-09-2006, 01:44 AM
Application.ScreenUpdating = False
Dim iToday As Integer
Dim iYesterday As Integer
Dim iStart As Long
Selection.CurrentRegion.Select
Selection.NumberFormat = "dddd dd/mm/yyyy "
Selection.Font.Bold = True
iYesterday = Month(ActiveCell.Value)
iStart = ActiveCell.Row
Do Until IsEmpty(ActiveCell.Value)
With ActiveCell
.Offset(1, 0).Select
iToday = Month(.Value)
If iToday <> iYesterday Then
.EntireRow.Insert
.Offset(-1, 0).Value = .Row - iStart - 1
.Offset(-1, 0).NumberFormat = "General"
iStart = .Row
.Offset(1, 0).Select
End If
iYesterday = iToday
End With
Loop
With ActiveCell
.Value = .Row - iStart - 1
.NumberFormat = "General"
iStart = .Row
End With
ScreenUpdating = True

lior03
06-10-2006, 12:21 AM
hello
i wanted to sum up the second column.the one with the data .not the one with dates.
how can i make excel sum all data from precious empty cell to the current one.
thanks

lior03
06-10-2006, 12:28 AM
hello
something like:
dim cell as range
for each cell in selection
if is empty(cell) then
cell.formulaR1C1="=sum............
thanks
thanks

Bob Phillips
06-10-2006, 02:20 PM
You already change the first and second column to bold and date format. SO if the second contains numbers, why format as date?