Inserting blank rows plus using Sum

[Bill95051]

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.

 

[AltaEgo]

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

 

[Bill95051]

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

 

[Sheeloo]

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