B
Bernice
I have a spreadsheet that contains a large amount of data in 9 columns. I'm
currently doing a row insert and several col sums with a maco but now need to
add some additional functionality.
I would like to perform a row sum on the col total
1014489102 BIL 7/30/2009 59.27 28.24 13.5 0 3 104.01
1014489102 PEN 8/25/2009 5.93 2.82 1.35 0 0.3 114.41
1014489102 PAY 8/26/2009 -59.27 -28.24 -13.5 0 -3 10.4
1014489102 BIL 8/30/2009 51.99 28.24 13.5 0 3 107.13
Total 21.34 -6.07 0 0 0.01 15.28
If the total row sum matches with the prev cell above, then do nothing. If
they don't match shade the total row. Also if the total row sum matches with
the previous cell and the four columns are not the same, then copy the data
from the total column sums to the previous entry, for example if I had in the
total column 60, 20 and 27.13 in the first three columns, then these values
would be copied into the entry for 8/30/09.
Also, here is the current macro that I'm using
Sub InsertRows()
Dim rngCell As Excel.Range
Dim rngSum As Excel.Range
Dim rngData As Excel.Range
Dim DataColumn As Long
Dim i As Long
Application.ScreenUpdating = False
DataColumn = 1
Set rngCell = Cells(2, DataColumn)
Set rngRow = Cells(Rows.Count, DataColumn).End(xlUp)
Set rngData = Range(rngCell, rngRow)
Set rngRow = rngCell
Do
If rngCell.Value <> rngCell(2, 1).Value Then
rngCell(2, 1).EntireRow.Resize(3).Insert
For i = 4 To 8
Set rngSum = Range(rngRow(1, i), rngCell(1, i))
rngCell(2, i).Value = Application.Sum(rngSum)
rngCell(2, 1).Value = "Total"
rngCell(2, 1).Font.Bold = True
Next i
Set rngCell = rngCell(5, 1)
Set rngRow = rngCell
Else
Set rngCell = rngCell(2, 1)
End If
Loop Until Application.Intersect(rngCell, rngData) Is Nothing
Application.ScreenUpdating = True
End Sub
currently doing a row insert and several col sums with a maco but now need to
add some additional functionality.
I would like to perform a row sum on the col total
1014489102 BIL 7/30/2009 59.27 28.24 13.5 0 3 104.01
1014489102 PEN 8/25/2009 5.93 2.82 1.35 0 0.3 114.41
1014489102 PAY 8/26/2009 -59.27 -28.24 -13.5 0 -3 10.4
1014489102 BIL 8/30/2009 51.99 28.24 13.5 0 3 107.13
Total 21.34 -6.07 0 0 0.01 15.28
If the total row sum matches with the prev cell above, then do nothing. If
they don't match shade the total row. Also if the total row sum matches with
the previous cell and the four columns are not the same, then copy the data
from the total column sums to the previous entry, for example if I had in the
total column 60, 20 and 27.13 in the first three columns, then these values
would be copied into the entry for 8/30/09.
Also, here is the current macro that I'm using
Sub InsertRows()
Dim rngCell As Excel.Range
Dim rngSum As Excel.Range
Dim rngData As Excel.Range
Dim DataColumn As Long
Dim i As Long
Application.ScreenUpdating = False
DataColumn = 1
Set rngCell = Cells(2, DataColumn)
Set rngRow = Cells(Rows.Count, DataColumn).End(xlUp)
Set rngData = Range(rngCell, rngRow)
Set rngRow = rngCell
Do
If rngCell.Value <> rngCell(2, 1).Value Then
rngCell(2, 1).EntireRow.Resize(3).Insert
For i = 4 To 8
Set rngSum = Range(rngRow(1, i), rngCell(1, i))
rngCell(2, i).Value = Application.Sum(rngSum)
rngCell(2, 1).Value = "Total"
rngCell(2, 1).Font.Bold = True
Next i
Set rngCell = rngCell(5, 1)
Set rngRow = rngCell
Else
Set rngCell = rngCell(2, 1)
End If
Loop Until Application.Intersect(rngCell, rngData) Is Nothing
Application.ScreenUpdating = True
End Sub