VBA Macro for Tallying transactions

H

Helixal

New Member
Joined
Apr 25, 2012
Messages
18
I have a macro shown below which generates the following spreadsheet. It creates columns E, I and J from an imported CSV spreadsheet.. Column E is the result of totaling the transactions in column D. Column I does the same for column H. It does this until the quantity in column E reaches 0. I.e. the stock is completely liquidated. There is a problem that I want to correct. If after liquidating a stock I repurchase the same stock the results are incorrect. Notice the case of Covad below . It is completely liquidated in the second transaction. The third transaction should start fresh with a new net total of 300 and 3.00/share. Instead it is picking up the + 3.20 from the prior net total. One thing the capture tool did not pick up is a solid heavy line drawn between investments (i.e. when the investment changes name.) This heavy line should also be drawn between the second and third Covad transaction. Notice also that when a stock is liquidated the font for that stock changes to italic.

Excel 2010
A
B
C
D
E
F
G
H
I
J
1/1/1901 through 4/15/2016
2007/07/24
CBS Corp. Cl B
Buy
2011/12/31
CBS Corp. Cl B
Rem.Shs
Sold
2007/07/24
Covad
Buy
2008/04/21
Covad
Sell
Sold
2009/01/01
Covad
Buy
2007/07/24
Gabelli Utility Trust Closed End
Buy
2007/09/24
Gabelli Utility Trust Closed End
Rnv.Div
2007/11/06
Gabelli Utility Trust Closed End
Sell
2012/12/14
Gabelli Utility Trust Closed End
Buy
2007/07/24
Idearc
Buy
2010/01/04
Idearc
Sell
Sold
2007/07/24
Marathon Oil Corporation
Buy
2011/06/30
Marathon Oil Corporation
Rem.Shs
2007/07/24
Mesa Offshore Trust
Buy

<tbody abp="1582">
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Date
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Investment
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Activity
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Quantity
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Net Quant
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Price/Sh
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Comm
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Total
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]Net Total
[/TD]
[TD="bgcolor: #E2E2E2, align: center"]NetPr/Sh
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"]271.00
[/TD]
[TD="align: right"]271.00
[/TD]
[TD="align: right"]34.33
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-9303.43
[/TD]
[TD="align: right"]-9303.43
[/TD]
[TD="align: right"]-34.33
[/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"]-271.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]-9303.43
[/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"]2.00
[/TD]
[TD="align: right"]2.00
[/TD]
[TD="align: right"]08.60
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-17.20
[/TD]
[TD="align: right"]-17.20
[/TD]
[TD="align: right"]-0.86
[/TD]

[TD="align: center"]8
[/TD]

[TD="align: right"]-2.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]10.20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20.40
[/TD]
[TD="align: right"]03.20
[/TD]

[TD="align: center"]9
[/TD]

[TD="align: right"]100.00
[/TD]
[TD="align: right"]100.00
[/TD]
[TD="align: right"]3.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-300.00
[/TD]
[TD="align: right"]-296.80
[/TD]
[TD="align: right"]-2.96
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: right"]11.40
[/TD]
[TD="align: right"]11.40
[/TD]
[TD="align: right"]9.83
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-1124.55
[/TD]
[TD="align: right"]-1124.55
[/TD]
[TD="align: right"]-98.64
[/TD]

[TD="align: center"]11
[/TD]

[TD="align: right"]15.67
[/TD]
[TD="align: right"]27.07
[/TD]
[TD="align: right"]8.82
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-138.11
[/TD]
[TD="align: right"]-1262.66
[/TD]
[TD="align: right"]-46.65
[/TD]

[TD="align: center"]12
[/TD]

[TD="align: right"]-0.67
[/TD]
[TD="align: right"]26.40
[/TD]
[TD="align: right"]9.17
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.12
[/TD]
[TD="align: right"]-1256.54
[/TD]
[TD="align: right"]-47.60
[/TD]

[TD="align: center"]13
[/TD]

[TD="align: right"]341.00
[/TD]
[TD="align: right"]367.40
[/TD]
[TD="align: right"]6.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-2046.00
[/TD]
[TD="align: right"]-3302.54
[/TD]
[TD="align: right"]-8.99
[/TD]

[TD="align: center"]14
[/TD]

[TD="align: right"]56.00
[/TD]
[TD="align: right"]56.00
[/TD]
[TD="align: right"]36.12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-2022.72
[/TD]
[TD="align: right"]-2022.72
[/TD]
[TD="align: right"]-36.12
[/TD]

[TD="align: center"]15
[/TD]

[TD="align: right"]-56.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]-2022.72
[/TD]

[TD="align: center"]16
[/TD]

[TD="align: right"]100.00
[/TD]
[TD="align: right"]100.00
[/TD]
[TD="align: right"]58.06
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-5806.00
[/TD]
[TD="align: right"]-5806.00
[/TD]
[TD="align: right"]-58.06
[/TD]

[TD="align: center"]17
[/TD]

[TD="align: right"]-1000.00
[/TD]
[TD="align: right"]-900.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]-5806.00
[/TD]
[TD="align: right"]6.45
[/TD]

[TD="align: center"]18
[/TD]

[TD="align: right"]500.00
[/TD]
[TD="align: right"]500.00
[/TD]
[TD="align: right"]0.09
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-45.00
[/TD]
[TD="align: right"]-45.00
[/TD]
[TD="align: right"]-0.09
[/TD]

</tbody>

Sorry I could not get rid of this stuff below here. Scroll down to see the macro in question
[TABLE="width: 85%"]
<tbody abp="1979">[TR]
[TD]
[TABLE="width: 100%"]
<tbody abp="1984">[TR="bgcolor: #DAE7F5"]
[TH="width: 10"][/TH]
[TH="align: left"][/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J1
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J2
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A3
[/TH]
[TD="align: left"]1/1/1901 through 4/15/2016
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J3
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A4
[/TH]
[TD="align: left"]Date
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B4
[/TH]
[TD="align: left"]Investment
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4
[/TH]
[TD="align: left"]Activity
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D4
[/TH]
[TD="align: left"]Quantity
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E4
[/TH]
[TD="align: left"]Net Quant
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F4
[/TH]
[TD="align: left"]Price/Sh
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G4
[/TH]
[TD="align: left"]Comm
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H4
[/TH]
[TD="align: left"]Total
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I4
[/TH]
[TD="align: left"]Net Total
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J4
[/TH]
[TD="align: left"]NetPr/Sh
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A5
[/TH]
[TD="align: left"]39287
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5
[/TH]
[TD="align: left"]CBS Corp. Cl B
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D5
[/TH]
[TD="align: left"]271
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E5
[/TH]
[TD="align: left"]271
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F5
[/TH]
[TD="align: left"]34.33
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G5
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H5
[/TH]
[TD="align: left"]-9303.43
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I5
[/TH]
[TD="align: left"]-9303.43
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J5
[/TH]
[TD="align: left"]-34.33
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A6
[/TH]
[TD="align: left"]40908
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6
[/TH]
[TD="align: left"]CBS Corp. Cl B
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6
[/TH]
[TD="align: left"]Rem.Shs
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D6
[/TH]
[TD="align: left"]-271
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E6
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F6
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G6
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H6
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I6
[/TH]
[TD="align: left"]-9303.43
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J6
[/TH]
[TD="align: left"]Sold
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A7
[/TH]
[TD="align: left"]39287
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7
[/TH]
[TD="align: left"]Covad
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C7
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D7
[/TH]
[TD="align: left"]2
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7
[/TH]
[TD="align: left"]2
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F7
[/TH]
[TD="align: left"]0.86
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G7
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H7
[/TH]
[TD="align: left"]-1.72
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I7
[/TH]
[TD="align: left"]-1.72
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J7
[/TH]
[TD="align: left"]-0.86
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A8
[/TH]
[TD="align: left"]39559
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B8
[/TH]
[TD="align: left"]Covad
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C8
[/TH]
[TD="align: left"]Sell
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D8
[/TH]
[TD="align: left"]-2
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E8
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F8
[/TH]
[TD="align: left"]1.02
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G8
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H8
[/TH]
[TD="align: left"]2.04
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I8
[/TH]
[TD="align: left"]0.32
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J8
[/TH]
[TD="align: left"]Sold
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A9
[/TH]
[TD="align: left"]39814
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B9
[/TH]
[TD="align: left"]Covad
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C9
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D9
[/TH]
[TD="align: left"]100
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E9
[/TH]
[TD="align: left"]100
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F9
[/TH]
[TD="align: left"]3
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G9
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H9
[/TH]
[TD="align: left"]-300
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I9
[/TH]
[TD="align: left"]-299.68
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J9
[/TH]
[TD="align: left"]-2.9968
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A10
[/TH]
[TD="align: left"]39287
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B10
[/TH]
[TD="align: left"]Gabelli Utility Trust Closed End
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C10
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D10
[/TH]
[TD="align: left"]11.4
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E10
[/TH]
[TD="align: left"]11.4
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F10
[/TH]
[TD="align: left"]9.83
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G10
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H10
[/TH]
[TD="align: left"]-1124.552
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I10
[/TH]
[TD="align: left"]-1124.552
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J10
[/TH]
[TD="align: left"]-98.6449122807017
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A11
[/TH]
[TD="align: left"]39349
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B11
[/TH]
[TD="align: left"]Gabelli Utility Trust Closed End
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C11
[/TH]
[TD="align: left"]Rnv.Div
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D11
[/TH]
[TD="align: left"]15.6673
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E11
[/TH]
[TD="align: left"]27.0673
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F11
[/TH]
[TD="align: left"]8.82
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G11
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H11
[/TH]
[TD="align: left"]-138.11
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I11
[/TH]
[TD="align: left"]-1262.662
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J11
[/TH]
[TD="align: left"]-46.6489823513982
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A12
[/TH]
[TD="align: left"]39392
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B12
[/TH]
[TD="align: left"]Gabelli Utility Trust Closed End
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C12
[/TH]
[TD="align: left"]Sell
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D12
[/TH]
[TD="align: left"]-0.6673
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E12
[/TH]
[TD="align: left"]26.4
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F12
[/TH]
[TD="align: left"]9.17
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G12
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H12
[/TH]
[TD="align: left"]6.12
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I12
[/TH]
[TD="align: left"]-1256.542
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J12
[/TH]
[TD="align: left"]-47.5962878787879
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A13
[/TH]
[TD="align: left"]41257
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B13
[/TH]
[TD="align: left"]Gabelli Utility Trust Closed End
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C13
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D13
[/TH]
[TD="align: left"]341
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E13
[/TH]
[TD="align: left"]367.4
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F13
[/TH]
[TD="align: left"]6
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G13
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H13
[/TH]
[TD="align: left"]-2046
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I13
[/TH]
[TD="align: left"]-3302.542
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J13
[/TH]
[TD="align: left"]-8.98895481763745
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A14
[/TH]
[TD="align: left"]39287
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B14
[/TH]
[TD="align: left"]Idearc
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C14
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D14
[/TH]
[TD="align: left"]56
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E14
[/TH]
[TD="align: left"]56
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F14
[/TH]
[TD="align: left"]36.12
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G14
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H14
[/TH]
[TD="align: left"]-2022.72
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I14
[/TH]
[TD="align: left"]-2022.72
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J14
[/TH]
[TD="align: left"]-36.12
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A15
[/TH]
[TD="align: left"]40182
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B15
[/TH]
[TD="align: left"]Idearc
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C15
[/TH]
[TD="align: left"]Sell
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D15
[/TH]
[TD="align: left"]-56
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E15
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F15
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G15
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H15
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I15
[/TH]
[TD="align: left"]-2022.72
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J15
[/TH]
[TD="align: left"]Sold
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A16
[/TH]
[TD="align: left"]39287
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B16
[/TH]
[TD="align: left"]Marathon Oil Corporation
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C16
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D16
[/TH]
[TD="align: left"]100
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E16
[/TH]
[TD="align: left"]100
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F16
[/TH]
[TD="align: left"]58.06
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G16
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H16
[/TH]
[TD="align: left"]-5806
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I16
[/TH]
[TD="align: left"]-5806
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J16
[/TH]
[TD="align: left"]-58.06
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A17
[/TH]
[TD="align: left"]40724
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B17
[/TH]
[TD="align: left"]Marathon Oil Corporation
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C17
[/TH]
[TD="align: left"]Rem.Shs
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D17
[/TH]
[TD="align: left"]-1000
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E17
[/TH]
[TD="align: left"]-900
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F17
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G17
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H17
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I17
[/TH]
[TD="align: left"]-5806
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J17
[/TH]
[TD="align: left"]6.45111111111111
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A18
[/TH]
[TD="align: left"]39287
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B18
[/TH]
[TD="align: left"]Mesa Offshore Trust
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C18
[/TH]
[TD="align: left"]Buy
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D18
[/TH]
[TD="align: left"]500
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E18
[/TH]
[TD="align: left"]500
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F18
[/TH]
[TD="align: left"]0.09
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G18
[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H18
[/TH]
[TD="align: left"]-45
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I18
[/TH]
[TD="align: left"]-45
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J18
[/TH]
[TD="align: left"]-0.09
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
The macro that that generates the above spreadsheet is here:

Sub Three_Sums()
' Sub Net_row5_Sums()
Dim LRow As Long
LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("H4") = "Net Quant."
Range("I4") = "Net Total"
Range("J4") = "NetPr/Sh"
Range("H5").Formula = "=D5+Sumif($B$1:B4,B5,$D$1:D4)"
Range("I5").Formula = "=G5+Sumif($B$1:B4,B5,$G$1:G4)"
Range("H5:I5").AutoFill Destination:=Range("H5:I" & LRow)
Range("H5:I" & LRow).Copy
Range("H5").PasteSpecial xlPasteValues
Columns("H").Cut
Columns("E").Insert Shift:=xlToRight

With Range("A5:J" & LRow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$B6<>$B5"
' .FormatConditions(1).Interior.ColorIndex = 15
.FormatConditions(1).Borders(xlBottom).Color = 15
End With
For r = LRow To 5 Step -1
If Range("E" & r).Value = 0 Then
Range("A" & r & ":J" & r).Font.Name = "TimesNewRoman"
Range("A" & r & ":J" & r).Font.Italic = True
' Range("A" & r & ":J" & r).Font.Strikethrough = True
'.FontStyle = "Italic"
' ColorIndex = 15 (Replace Name = "bimini")
End If
If Range("B" & r).Font.Italic = True And Range("B" & r - 1).Value = Range("B" & r).Value Then
' ColorIndex = 15
Range("A" & r - 1 & ":J" & r - 1).Font.Name = "TimesNewRoman"
Range("A" & r - 1 & ":J" & r - 1).Font.Italic = True
'ColorIndex = 15
End If
'Stuff added by Agv for column J cost per share:
If Range("E" & r).Value = 0 Then
Range("J" & r).Value = "Sold"
Else: Range("J" & r).Value = Range("I" & r) / Range("E" & r)
End If

Next r
Range("A1").Select
'With Selection.Font
' .Name = "Arial"
' .FontStyle = "Regular"
' .Size = 10
' .Strikethrough = False
' .Superscript = False
' .Subscript = False
' .OutlineFont = False
' .Shadow = False
' .underline = xlUnderlineStyleNone
' .ColorIndex = 48
' End With
End Sub


Help in modifying the maxcro would be appreciated.

Helixal
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Hi guys,

I thought I would re-post the code properly as follows below in this post. Please refer to the prior post to get an explanation of the problem.

Code: 
Sub Three_Sums()

' Sub Net_row5_Sums()

Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("H4") = "Net Quant."
Range("I4") = "Net Total"
Range("J4") = "NetPr/Sh"
Range("H5").Formula = "=D5+Sumif($B$1:B4,B5,$D$1:D4)"
Range("I5").Formula = "=G5+Sumif($B$1:B4,B5,$G$1:G4)"
Range("H5:I5").AutoFill Destination:=Range("H5:I" & LRow)
Range("H5:I" & LRow).Copy
Range("H5").PasteSpecial xlPasteValues
Columns("H").Cut
Columns("E").Insert Shift:=xlToRight


With Range("A5:J" & LRow)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$B6<>$B5"
'   .FormatConditions(1).Interior.ColorIndex = 15
    .FormatConditions(1).Borders(xlBottom).Color = 15

End With

For r = LRow To 5 Step -1
    If Range("E" & r).Value = 0 Then
       Range("A" & r & ":J" & r).Font.Name = "TimesNewRoman"
        Range("A" & r & ":J" & r).Font.Italic = True
   '     Range("A" & r & ":J" & r).Font.Strikethrough = True
  '.FontStyle = "Italic"
 '      ColorIndex = 15 (Replace Name = "bimini")
    End If
    If Range("B" & r).Font.Italic = True And Range("B" & r - 1).Value = Range("B" & r).Value Then
   ' ColorIndex = 15
       Range("A" & r - 1 & ":J" & r - 1).Font.Name = "TimesNewRoman"
       Range("A" & r - 1 & ":J" & r - 1).Font.Italic = True
   'ColorIndex = 15
    End If
'Stuff added by Agv for column J cost per share:

    If Range("E" & r).Value = 0 Then
        Range("J" & r).Value = "Sold"
    Else: Range("J" & r).Value = Range("I" & r) / Range("E" & r)
    End If
    
Next r

Range("A1").Select
'With Selection.Font
'        .Name = "Arial"
'        .FontStyle = "Regular"
'        .Size = 10
'        .Strikethrough = False
'        .Superscript = False
'        .Subscript = False
'        .OutlineFont = False
'        .Shadow = False
'        .underline = xlUnderlineStyleNone
'        .ColorIndex = 48
 '   End With
End Sub


Any suggestions would be appreciated.

AL
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Conditional macro
Replies
8
Views
456
Fluff
Fluff
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
930
paulkc
P
Guna13
  • Question Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
1K
Guna13
Guna13
E
  • Question Question
FIFO Macro giving different results each time it runs
Replies
7
Views
1K
Lee Wei Sin
L
J
  • Question Question
Macro to delete all rows where column A is actually blank or "", on specified sheets
Replies
1
Views
422
rollis13
rollis13

Forum statistics

Threads
1,224,602
Messages
6,179,848
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top