Iam trying to create VBA code to compare the values in a single column andperform a calculation if the condition exist. The below table is a sample of mydata. The 1st 5 numbers of the Security is always a unique number and alwaysend with US. The difference is some lines end with an L (meaning long) or an S(meaning short). When the there is a L and short (ie 74724USL and 74724USS), Ineed to sum the values in the Market Value column which will give me the net. Ineed to do this again for Carry Value and again for Book Value. There are a fewmore value columns in my worksheet but this give the basis of my need.
I can have a new column inserted, say Net MarketValue to hold the new value, etc. When done delete the short row. Open tosuggestions
[TABLE="width: 331"]
<tbody>[TR]
[TD="bgcolor: transparent"] ASOF
[/TD]
[TD="bgcolor: transparent"] Cost
Basis
[/TD]
[TD="bgcolor: transparent"] Security
[/TD]
[TD="width: 74, bgcolor: transparent"] Market
Value
[/TD]
[TD="bgcolor: transparent"] Carry
Value
[/TD]
[TD="bgcolor: transparent"] Book
Value
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74677US
[/TD]
[TD="width: 74, bgcolor: transparent"] 3956.51
[/TD]
[TD="bgcolor: transparent"] 1735.82
[/TD]
[TD="bgcolor: transparent"] 3956.51
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74678US
[/TD]
[TD="width: 74, bgcolor: transparent"] 2519.45
[/TD]
[TD="bgcolor: transparent"] 1250.9
[/TD]
[TD="bgcolor: transparent"] 2519.45
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74679US
[/TD]
[TD="width: 74, bgcolor: transparent"] 2737.88
[/TD]
[TD="bgcolor: transparent"] 857.62
[/TD]
[TD="bgcolor: transparent"] 2737.88
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74724USL
[/TD]
[TD="width: 74, bgcolor: transparent"] 186373.59
[/TD]
[TD="bgcolor: transparent"] 154.7
[/TD]
[TD="bgcolor: transparent"] 186373.59
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74724USS
[/TD]
[TD="width: 74, bgcolor: transparent"] -61866.54
[/TD]
[TD="bgcolor: transparent"] -0.01
[/TD]
[TD="bgcolor: transparent"] -61866.54
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74725USL
[/TD]
[TD="width: 74, bgcolor: transparent"] 151186.67
[/TD]
[TD="bgcolor: transparent"] 788.07
[/TD]
[TD="bgcolor: transparent"] 151186.67
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74725USS
[/TD]
[TD="width: 74, bgcolor: transparent"] -51404.1
[/TD]
[TD="bgcolor: transparent"] -0.01
[/TD]
[TD="bgcolor: transparent"] -51404.1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74726USL
[/TD]
[TD="width: 74, bgcolor: transparent"] 176759.57
[/TD]
[TD="bgcolor: transparent"] 2478.61
[/TD]
[TD="bgcolor: transparent"] 176759.57
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74726USS
[/TD]
[TD="width: 74, bgcolor: transparent"] -62673.08
[/TD]
[TD="bgcolor: transparent"] -0.01
[/TD]
[TD="bgcolor: transparent"] -62673.08
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74727US
[/TD]
[TD="width: 74, bgcolor: transparent"] 260968.23
[/TD]
[TD="bgcolor: transparent"] 3742.91
[/TD]
[TD="bgcolor: transparent"] 260968.23
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74920US
[/TD]
[TD="width: 74, bgcolor: transparent"] 3311.83
[/TD]
[TD="bgcolor: transparent"] 2918.47
[/TD]
[TD="bgcolor: transparent"] 3311.83
[/TD]
[/TR]
</tbody>[/TABLE]
I can have a new column inserted, say Net MarketValue to hold the new value, etc. When done delete the short row. Open tosuggestions
[TABLE="width: 331"]
<tbody>[TR]
[TD="bgcolor: transparent"] ASOF
[/TD]
[TD="bgcolor: transparent"] Cost
Basis
[/TD]
[TD="bgcolor: transparent"] Security
[/TD]
[TD="width: 74, bgcolor: transparent"] Market
Value
[/TD]
[TD="bgcolor: transparent"] Carry
Value
[/TD]
[TD="bgcolor: transparent"] Book
Value
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74677US
[/TD]
[TD="width: 74, bgcolor: transparent"] 3956.51
[/TD]
[TD="bgcolor: transparent"] 1735.82
[/TD]
[TD="bgcolor: transparent"] 3956.51
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74678US
[/TD]
[TD="width: 74, bgcolor: transparent"] 2519.45
[/TD]
[TD="bgcolor: transparent"] 1250.9
[/TD]
[TD="bgcolor: transparent"] 2519.45
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74679US
[/TD]
[TD="width: 74, bgcolor: transparent"] 2737.88
[/TD]
[TD="bgcolor: transparent"] 857.62
[/TD]
[TD="bgcolor: transparent"] 2737.88
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74724USL
[/TD]
[TD="width: 74, bgcolor: transparent"] 186373.59
[/TD]
[TD="bgcolor: transparent"] 154.7
[/TD]
[TD="bgcolor: transparent"] 186373.59
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74724USS
[/TD]
[TD="width: 74, bgcolor: transparent"] -61866.54
[/TD]
[TD="bgcolor: transparent"] -0.01
[/TD]
[TD="bgcolor: transparent"] -61866.54
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74725USL
[/TD]
[TD="width: 74, bgcolor: transparent"] 151186.67
[/TD]
[TD="bgcolor: transparent"] 788.07
[/TD]
[TD="bgcolor: transparent"] 151186.67
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74725USS
[/TD]
[TD="width: 74, bgcolor: transparent"] -51404.1
[/TD]
[TD="bgcolor: transparent"] -0.01
[/TD]
[TD="bgcolor: transparent"] -51404.1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74726USL
[/TD]
[TD="width: 74, bgcolor: transparent"] 176759.57
[/TD]
[TD="bgcolor: transparent"] 2478.61
[/TD]
[TD="bgcolor: transparent"] 176759.57
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74726USS
[/TD]
[TD="width: 74, bgcolor: transparent"] -62673.08
[/TD]
[TD="bgcolor: transparent"] -0.01
[/TD]
[TD="bgcolor: transparent"] -62673.08
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74727US
[/TD]
[TD="width: 74, bgcolor: transparent"] 260968.23
[/TD]
[TD="bgcolor: transparent"] 3742.91
[/TD]
[TD="bgcolor: transparent"] 260968.23
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 03/31/2019
[/TD]
[TD="bgcolor: transparent"] GAAP
[/TD]
[TD="bgcolor: transparent"] 74920US
[/TD]
[TD="width: 74, bgcolor: transparent"] 3311.83
[/TD]
[TD="bgcolor: transparent"] 2918.47
[/TD]
[TD="bgcolor: transparent"] 3311.83
[/TD]
[/TR]
</tbody>[/TABLE]