Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - SumRow, Cell Shading, Copy Data
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it possible to sort the data by cell shading or cell color? | Excel Discussion (Misc queries) | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
macro to copy & paste cell data into another cell | Excel Programming | |||
Is there a formula, function or macro that accounts for shading of a cell or row? | Excel Discussion (Misc queries) | |||
Writing a macro to copy first cell down until next cell has data | Excel Programming |