-
summing months
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
[VBA]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[/VBA]
thanks
moshe
-
Is there a reason you don't want to use either subtotals or pivot tables for this?
-
[vba]
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
[/vba]
-
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
moshe
-
hello
something like:
[VBA]dim cell as range
for each cell in selection
if is empty(cell) then
cell.formulaR1C1="=sum............
thanks[/VBA]
thanks
moshe
-
You already change the first and second column to bold and date format. SO if the second contains numbers, why format as date?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules