If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Inserting blank rows plus using Sum
I have a list of stocks sorted by date. When the stock symbol changes, I
need to insert 2 blank rows and AutoSum columns 2, 6,7, and 8 of the group and display them on the first of the 2 blank rows, leaving a blank row before the next group. Symbol Qty Price Action Name US Trade Date Amount Commission Fees MSFT -210 28.95 Sell 2/13/2008 6079.43 -0.07 -0.07 MSFT -790 28.95 Sell 2/13/2008 22863.24 -7.26 -0.26 -1000 28942.67 -7.33 -0.33 C -700 22.16 Sell 3/4/2008 15511.82 -0.18 -0.18 C -300 22.15 Sell 3/4/2008 6637.92 -7.08 -0.08 -1000 22149.74 -7.26 -0.26 MSFT -500 27.5712 Sell 3/4/2008 13785.44 -0.16 -0.16 MSFT -440 27.57 Sell 3/4/2008 12130.66 -0.14 -0.14 MSFT -60 27.57 Sell 3/4/2008 1647.18 -7.02 -0.02 -1000 27563.28 -7.32 -0.32 WM -700 12.01 Sell 3/13/2008 8406.9 -0.1 -0.1 WM -70 12.01 Sell 3/13/2008 840.69 -0.01 -0.01 WM -600 12.01 Sell 3/13/2008 7205.92 -0.08 -0.08 WM -30 12.01 Sell 3/13/2008 353.29 -7.01 -0.01 WM -600 12.01 Sell 3/13/2008 7205.92 -0.08 -0.08 I modified "Inserting a Row (update)", by Tom (General Questions) to insert a second row, but am stumped about adding SUM for the Qty, Amount, Commission, and Fees (columns 2, 6,7, and 8) while doing so. Sub SumAndSeparate() ' originally called "InsertRows" StartRow = 3 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) "" If Cells(i, DataColumn) Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert Cells(i, DataColumn).EntireRow.Insert ' a 2nd blank row added i = i + 2 End If i = i + 1 Wend End Sub I am using Excel 2007 but need to export it to MS Works when finished (which shouldn't matter too much since most of the work is done in Excel). If the needed actions can be added on/in to the macro above, that would be great. I'll be able to understand it. While I \have programmed before, I have no background in Visual Basic and my macro talents are fossilized. |
#2
|
|||
|
|||
Inserting blank rows plus using Sum
It may be possible to do this more efficiently but the sub below will insert
totals per your example. Call the Sub after you insert your two blank rows. I did not develop a routine to check that there are two blank rows between sections so be careful to call it once only. Calling it twice or more does modify existing data but could cause confusion. Modified substantially from code located at http://www.mrexcel.com/archive/VBA/4272.html Sub insert_sum_values() Dim sum_of_range, tmp For i = 2 To 8 If i = 2 Or i = 6 Or i = 7 Or i = 8 Then 'column numbers where sums required Cells(3, i).Select 'first cell at top of range to be summed Do Range(ActiveCell, ActiveCell.End(xlDown)).Select tmp = ActiveCell.Value If tmp "" Then sum_of_range = Application.WorksheetFunction.Sum(Selection) ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range ActiveCell.End(xlDown).Offset(2, 0).Select Else sum_of_range = "" End If Loop Until sum_of_range = "" End If Next i End Sub -- Steve "Bill95051" wrote in message news I have a list of stocks sorted by date. When the stock symbol changes, I need to insert 2 blank rows and AutoSum columns 2, 6,7, and 8 of the group and display them on the first of the 2 blank rows, leaving a blank row before the next group. Symbol Qty Price Action Name US Trade Date Amount Commission Fees MSFT -210 28.95 Sell 2/13/2008 6079.43 -0.07 -0.07 MSFT -790 28.95 Sell 2/13/2008 22863.24 -7.26 -0.26 -1000 28942.67 -7.33 -0.33 C -700 22.16 Sell 3/4/2008 15511.82 -0.18 -0.18 C -300 22.15 Sell 3/4/2008 6637.92 -7.08 -0.08 -1000 22149.74 -7.26 -0.26 MSFT -500 27.5712 Sell 3/4/2008 13785.44 -0.16 -0.16 MSFT -440 27.57 Sell 3/4/2008 12130.66 -0.14 -0.14 MSFT -60 27.57 Sell 3/4/2008 1647.18 -7.02 -0.02 -1000 27563.28 -7.32 -0.32 WM -700 12.01 Sell 3/13/2008 8406.9 -0.1 -0.1 WM -70 12.01 Sell 3/13/2008 840.69 -0.01 -0.01 WM -600 12.01 Sell 3/13/2008 7205.92 -0.08 -0.08 WM -30 12.01 Sell 3/13/2008 353.29 -7.01 -0.01 WM -600 12.01 Sell 3/13/2008 7205.92 -0.08 -0.08 I modified "Inserting a Row (update)", by Tom (General Questions) to insert a second row, but am stumped about adding SUM for the Qty, Amount, Commission, and Fees (columns 2, 6,7, and 8) while doing so. Sub SumAndSeparate() ' originally called "InsertRows" StartRow = 3 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) "" If Cells(i, DataColumn) Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert Cells(i, DataColumn).EntireRow.Insert ' a 2nd blank row added i = i + 2 End If i = i + 1 Wend End Sub I am using Excel 2007 but need to export it to MS Works when finished (which shouldn't matter too much since most of the work is done in Excel). If the needed actions can be added on/in to the macro above, that would be great. I'll be able to understand it. While I \have programmed before, I have no background in Visual Basic and my macro talents are fossilized. |
#3
|
|||
|
|||
Inserting blank rows plus using Sum
"AltaEgo" wrote: It may be possible to do this more efficiently but the sub below will insert totals per your example. Call the Sub after you insert your two blank rows. I did not develop a routine to check that there are two blank rows between sections so be careful to call it once only. Calling it twice or more does modify existing data but could cause confusion. Modified substantially from code located at http://www.mrexcel.com/archive/VBA/4272.html Thanks. I took your macro and tweaked some more. Here is what came out. I changed the variable i to row and the Sub-sub's variable to col (they conflicted with each other and also made it more readable). Passed the row info to the Sub-sub. Had to change ActiveCell to ...End(xlup) instead of down and trimmed out a few extra lines. Still have a problem with accurate Summing from group to group. Somehow the Sum function still gets the wrong column size. The original macro would do the first change in symbol, but then ran to the bottom and Summed the whole remaining column. Sub SumAndSeparate() StartRow = 3 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is row = StartRow + 1 While Cells(row, DataColumn) "" If Cells(row, DataColumn) Cells(row - 1, DataColumn) Then Cells(row, DataColumn).EntireRow.Insert Cells(row, DataColumn).EntireRow.Insert SumValues (row) row = row + 2 End If row = row + 1 Wend End Sub Sub SumValues(row) Dim sum_of_range, tmp For col = 2 To 8 If col = 2 Or col = 6 Or col = 7 Or col = 8 Then 'column numbers where sums required Cells(row, col).Select 'first cell at top of range to be summed tmp = ActiveCell.Value If ActiveCell.Value() = None Then Range(ActiveCell.Offset(-2), ActiveCell.End(xlUp)).Select ' Range(ActiveCell, ActiveCell.End(xlUp)).Select sum_of_range = Application.WorksheetFunction.Sum(Selection) ActiveCell.Offset(2, 0).Value = sum_of_range Else sum_of_range = "" End If End If ' Range(ActiveCell.Offset(1)).Select Next col Cells(row + 2, col - 7).Select End Sub Thanks for the idea Bill |
#4
|
|||
|
|||
Inserting blank rows plus using Sum
Try this...
It will inserst a SUM formula Sub insert_sum_values() Dim sum_of_range, tmp For i = 2 To 8 If i = 2 Or i = 6 Or i = 7 Or i = 8 Then 'column numbers where sums required Cells(3, i).Select 'first cell at top of range to be summed Do Range(ActiveCell, ActiveCell.End(xlDown)).Select tmp = ActiveCell.Value If tmp "" Then sum_of_range = "=SUM(" & Selection.Address & ")" ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range ActiveCell.End(xlDown).Offset(2, 0).Select Else sum_of_range = "" End If Loop Until sum_of_range = "" End If Next i End Sub ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Bill95051" wrote: "AltaEgo" wrote: It may be possible to do this more efficiently but the sub below will insert totals per your example. Call the Sub after you insert your two blank rows. I did not develop a routine to check that there are two blank rows between sections so be careful to call it once only. Calling it twice or more does modify existing data but could cause confusion. Modified substantially from code located at http://www.mrexcel.com/archive/VBA/4272.html Thanks. I took your macro and tweaked some more. Here is what came out. I changed the variable i to row and the Sub-sub's variable to col (they conflicted with each other and also made it more readable). Passed the row info to the Sub-sub. Had to change ActiveCell to ...End(xlup) instead of down and trimmed out a few extra lines. Still have a problem with accurate Summing from group to group. Somehow the Sum function still gets the wrong column size. The original macro would do the first change in symbol, but then ran to the bottom and Summed the whole remaining column. Sub SumAndSeparate() StartRow = 3 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is row = StartRow + 1 While Cells(row, DataColumn) "" If Cells(row, DataColumn) Cells(row - 1, DataColumn) Then Cells(row, DataColumn).EntireRow.Insert Cells(row, DataColumn).EntireRow.Insert SumValues (row) row = row + 2 End If row = row + 1 Wend End Sub Sub SumValues(row) Dim sum_of_range, tmp For col = 2 To 8 If col = 2 Or col = 6 Or col = 7 Or col = 8 Then 'column numbers where sums required Cells(row, col).Select 'first cell at top of range to be summed tmp = ActiveCell.Value If ActiveCell.Value() = None Then Range(ActiveCell.Offset(-2), ActiveCell.End(xlUp)).Select ' Range(ActiveCell, ActiveCell.End(xlUp)).Select sum_of_range = Application.WorksheetFunction.Sum(Selection) ActiveCell.Offset(2, 0).Value = sum_of_range Else sum_of_range = "" End If End If ' Range(ActiveCell.Offset(1)).Select Next col Cells(row + 2, col - 7).Select End Sub Thanks for the idea Bill |
Thread Tools | |
Display Modes | |
|
|