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

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



ASOF
Cost
Basis

Security
Market
Value

Carry
Value

Book
Value

03/31/2019
GAAP
74677US
3956.51
1735.82
3956.51
03/31/2019
GAAP
74678US
2519.45
1250.9
2519.45
03/31/2019
GAAP
74679US
2737.88
857.62
2737.88
03/31/2019
GAAP
74724USL
186373.59
154.7
186373.59
03/31/2019
GAAP
74724USS
-61866.54
-0.01
-61866.54
03/31/2019
GAAP
74725USL
151186.67
788.07
151186.67
03/31/2019
GAAP
74725USS
-51404.1
-0.01
-51404.1
03/31/2019
GAAP
74726USL
176759.57
2478.61
176759.57
03/31/2019
GAAP
74726USS
-62673.08
-0.01
-62673.08
03/31/2019
GAAP
74727US
260968.23
3742.91
260968.23
03/31/2019
GAAP
74920US
3311.83
2918.47
3311.83
<tbody> </tbody>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
A
B
C
D
E
F
G
H
I
1
ASOFCost BasisSecurityMarket ValueCarry ValueBook ValueNet Market ValueNet Carry ValueNet Book Value
2
03/31/2019GAAP74677US
3956.51​
1735.82​
3956.51​
3
03/31/2019GAAP74678US
2519.45​
1250.9​
2519.45​
4
03/31/2019GAAP74679US
2737.88​
857.62​
2737.88​
5
03/31/2019GAAP74724USL
186373.6​
154.7​
186373.59​
6
03/31/2019GAAP74724USS
-61866.5​
-0.01​
-61866.54​
7
03/31/2019GAAP74725USL
151186.7​
788.07​
151186.67​
8
03/31/2019GAAP74725USS
-51404.1​
-0.01​
-51404.1​
9
03/31/2019GAAP74726USL
176759.6​
2478.61​
176759.57​
10
03/31/2019GAAP74726USS
-62673.1​
-0.01​
-62673.08​
11
03/31/2019GAAP74727US
260968.2​
3742.91​
260968.23​
12
03/31/2019GAAP74920US
3311.83​
2918.47​
3311.83​
Sheet: Sheet1

After

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
1
ASOFCost BasisSecurityMarket ValueCarry ValueBook ValueNet Market ValueNet Carry ValueNet Book Value
2
03/31/2019GAAP74677US
3956.51​
1735.82​
3956.51​
3956.51​
1735.82​
3956.51​
3
03/31/2019GAAP74678US
2519.45​
1250.9​
2519.45​
2519.45​
1250.9​
2519.45​
4
03/31/2019GAAP74679US
2737.88​
857.62​
2737.88​
2737.88​
857.62​
2737.88​
5
03/31/2019GAAP74724USL
186373.6​
154.7​
186373.59​
124507.1​
154.69​
124507.1​
6
03/31/2019GAAP74725USL
151186.7​
788.07​
151186.67​
99782.57​
788.06​
99782.57​
7
03/31/2019GAAP74726USL
176759.6​
2478.61​
176759.57​
114086.5​
2478.6​
114086.5​
8
03/31/2019GAAP74727US
260968.2​
3742.91​
260968.23​
260968.2​
3742.91​
260968.2​
9
03/31/2019GAAP74920US
3311.83​
2918.47​
3311.83​
3311.83​
2918.47​
3311.83​
10
11
12
13
Sheet: Sheet1

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

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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