How to determine the accumulated number of shares bought for a Trade?

Question

I trade shares on Nasdaq and have unique trade numbers for each trade. Each unique trade number comprises of a number of share purchase transactions.

I am trying to calculate the number of shares that I bought for each unique trade number and allocate the accumulated number for each share purchase transaction to the adjacent column.

For example: Column A all the Trade Numbers are listed in sequence, Column B the number of Shares bought. I am trying to determine the accumulated number of shares that I have bought for each trade and place the result in Column C.

Trade Number	Number Shares Bought	Accumulated Number of Shares Bought
1	1	1
1	None	Must be blank
1	2	3
2	None	Must be blank
2	1	1
2	3	4
2	None	Must be blank

I created the following code. The IF statements do not produce the required output under Column C.

Sub SequentialShareNumber()

' ' SequentialShareNumber Macro ' Macro allocates a sequential number to each Share bought in a Trade

'Step 1: Declare variables Dim TradeRange As Range Dim TradeCell As Range

'Step 2: Define the Target Range Set TradeRange = Range("A2:A100")

'Step 3: Start looping through the range For Each TradeCell In TradeRange

'Step 4: If TradeCell.Value > 0 Then TradeCell.Select Dim ShareCounter As Integer ShareCounter = 0

'Step 5: Check if a Share was bought for this Trade Number in Column B TradeCell.Offset(0, 1).Range("A1").Select ActiveCell.Select

         If ActiveCell.Value = 1 Then
 
'Step 6: Allocate a sequential number to each Share bought for this Trade Number ShareCounter = ShareCounter + 1

'Step 7: Insert the sequential value in Column C ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.value = ShareCounter

        End If

End If
'Step 8: Get the next cell in the range Next TradeCell

End Sub

Raymond Wu · Answer 1 · 2021-04-18T13:27:00.090

Few things to note:

Full qualify your code

Avoid using select, you can perform most operation using range (read How to avoid using Select in Excel VBA for more details)

Sub SequentialShareNumber()
    Dim shareCounter As Long
    Dim tradeNumber As Long

    Const rowStart As Long = 2 'Starting row
    Const rowEnd As Long = 1000 'Ending row

    Dim i As Long
    Dim n As Long

    With Sheet1 'Change to the correct worksheet reference
        For i = rowStart To rowEnd  'Loop from row 2 to 1000
            If .Cells(i, 2).Value > 0 Then
                tradeNumber = .Cells(i, 1).Value
                For n = 2 To i ' Loop from starting row to the current for calculation
                    If .Cells(n, 1).Value = tradeNumber Then shareCounter = shareCounter + .Cells(n, 2).Value
                Next n
                .Cells(i, 3).Value = shareCounter
                shareCounter = 0
            End If
        Next i
    End With
End Sub

Awesome. Thank you Raymond for the neat approach. – Lawrence Horner May 16 '21 at 14:52 — Lawrence Horner, May 16 '21 at 14:52

How to determine the accumulated number of shares bought for a Trade?

1 Answers1