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

[perra34]

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]

 

[Yongle]

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

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

 

[perra34]

"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."