Create VBA code to compare the values in a single column and perform a calculation if the condition exist

P

perra34

New Member
Joined
Apr 24, 2019
Messages
2
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]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
VBA below returns the correct results based on the data provided
- if the security ends in USS, its values are added to the first matching USL equivalent

Your data includes ONE date and a maximum of ONE match per "USS " and "USL"
- is that typical of what your data contains?
- multiple matches not considered

Before

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]ASOF[/td][td]Cost Basis[/td][td]Security[/td][td]Market Value[/td][td]Carry Value[/td][td]Book Value[/td][td]Net Market Value[/td][td]Net Carry Value[/td][td]Net Book Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74677US[/td][td]
3956.51​
[/td][td]
1735.82​
[/td][td]
3956.51​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74678US[/td][td]
2519.45​
[/td][td]
1250.9​
[/td][td]
2519.45​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74679US[/td][td]
2737.88​
[/td][td]
857.62​
[/td][td]
2737.88​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74724USL[/td][td]
186373.6​
[/td][td]
154.7​
[/td][td]
186373.59​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74724USS[/td][td]
-61866.5​
[/td][td]
-0.01​
[/td][td]
-61866.54​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74725USL[/td][td]
151186.7​
[/td][td]
788.07​
[/td][td]
151186.67​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74725USS[/td][td]
-51404.1​
[/td][td]
-0.01​
[/td][td]
-51404.1​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74726USL[/td][td]
176759.6​
[/td][td]
2478.61​
[/td][td]
176759.57​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74726USS[/td][td]
-62673.1​
[/td][td]
-0.01​
[/td][td]
-62673.08​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74727US[/td][td]
260968.2​
[/td][td]
3742.91​
[/td][td]
260968.23​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74920US[/td][td]
3311.83​
[/td][td]
2918.47​
[/td][td]
3311.83​
[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

After

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]ASOF[/td][td]Cost Basis[/td][td]Security[/td][td]Market Value[/td][td]Carry Value[/td][td]Book Value[/td][td]Net Market Value[/td][td]Net Carry Value[/td][td]Net Book Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74677US[/td][td]
3956.51​
[/td][td]
1735.82​
[/td][td]
3956.51​
[/td][td]
3956.51​
[/td][td]
1735.82​
[/td][td]
3956.51​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74678US[/td][td]
2519.45​
[/td][td]
1250.9​
[/td][td]
2519.45​
[/td][td]
2519.45​
[/td][td]
1250.9​
[/td][td]
2519.45​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74679US[/td][td]
2737.88​
[/td][td]
857.62​
[/td][td]
2737.88​
[/td][td]
2737.88​
[/td][td]
857.62​
[/td][td]
2737.88​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74724USL[/td][td]
186373.6​
[/td][td]
154.7​
[/td][td]
186373.59​
[/td][td]
124507.1​
[/td][td]
154.69​
[/td][td]
124507.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74725USL[/td][td]
151186.7​
[/td][td]
788.07​
[/td][td]
151186.67​
[/td][td]
99782.57​
[/td][td]
788.06​
[/td][td]
99782.57​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74726USL[/td][td]
176759.6​
[/td][td]
2478.61​
[/td][td]
176759.57​
[/td][td]
114086.5​
[/td][td]
2478.6​
[/td][td]
114086.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74727US[/td][td]
260968.2​
[/td][td]
3742.91​
[/td][td]
260968.23​
[/td][td]
260968.2​
[/td][td]
3742.91​
[/td][td]
260968.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]03/31/2019[/td][td]GAAP[/td][td]74920US[/td][td]
3311.83​
[/td][td]
2918.47​
[/td][td]
3311.83​
[/td][td]
3311.83​
[/td][td]
2918.47​
[/td][td]
3311.83​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

VBA

Code: 
Sub NetValue()
    Dim Rng As Range, Cel As Range, Found As Range, Sec As String, Remove As Range
    With Sheets("Sheet1")
        Set Rng = .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    End With
[I][COLOR=#006400]'copy all values to Net columns[/COLOR][/I]
    Rng.Offset(, 1).Resize(, 3).Copy Rng.Resize(1, 1).Offset(, 4)
[I][COLOR=#006400]'replace values[/COLOR][/I]
    For Each Cel In Rng
        Sec = Right(Cel, 3)
        If Sec = "USS" Then
            Sec = Left(Cel, Len(Cel) - 3) & "USL"
            On Error Resume Next
            Set Found = Rng.Find(Sec)
            If Not Found Is Nothing Then
                If Not Remove Is Nothing Then Set Remove = Union(Remove, Cel) Else Set Remove = Cel
                    With Found
                        .Offset(, 4) = Cel.Offset(, 1) + .Offset(, 1)
                        .Offset(, 5) = Cel.Offset(, 2) + .Offset(, 2)
                        .Offset(, 6) = Cel.Offset(, 3) + .Offset(, 3)
                    End With
                End If
        End If
        Set Found = Nothing
    Next Cel
[I][COLOR=#006400]'delete short[/COLOR][/I]
    Remove.EntireRow.Delete
End Sub
 
Upvote 0
"Thank You very much. This worked! I had to make a few adjustments to the offset parms as I had other columns in my worksheet, in my example I only provide a sample."
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

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