Macro Help - SumRow, Cell Shading, Copy Data

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top