Running counter and referencing adjacent column

[shutterback]

I have a dataset that looks like this for 1400 rows:

Buy	273
Buy	263
Sell_short	340
Sell_long	209
Buy	271

<tbody>
</tbody>

When the left column is buy, the right column gets added to the account. When the left column is sell_short or sell_long, the right column gets detracted from the account. When the left column is sell_long, the counter after selling should be a positive number, and when the left column says sell_short, the counter after selling should be a negative number. I want to run a for loop through all of the rows, adding to the counter when left is buy and detracting when left is sell, and if the counter is negative but the left column says long, highlight the cell in red. If the counter is positive and the left column says short, highlight the cell in red. Otherwise, do nothing.

So far, I have

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #fffb00}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }span.s1 {color: #011993 }span.s2 {color: #000000 }</style>Sub counter()


Dim x As Range
Dim counter As Integer
counter = 0


For Each x In Range("D2", Range("D" & Rows.Count).End(xlUp))
If x.Offset(0, -1) = "Buy" Then
counter = counter + x
ElseIf x.Offset(0, -1).Value Like "*Sell*" Then
counter = counter - x
End If
Next x




End Sub






Need advice for where to go from here!

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this
You didn't say where you want the results, so i assumed results in column E.

Sub counter()
    Dim x As Range, counter As Long
    
    counter = 0
    For Each x In Range("D2", Range("D" & Rows.Count).End(xlUp))
        If x.Offset(0, -1) = "Buy" Then
            counter = counter + x
        ElseIf x.Offset(0, -1).Value Like "*Sell*" Then
            counter = counter - x
            If InStr(1, x.Offset(, -1), "long", vbTextCompare) And counter < 0 Then
                x.Offset(, 1).Interior.Color = vbRed
            ElseIf InStr(1, x.Offset(, -1), "short", vbTextCompare) And counter > 0 Then
                x.Offset(, 1).Interior.Color = vbRed
            End If
        End If
        x.Offset(, 1) = counter
    Next x
End Sub

Hope this helps

M.

 

[shutterback]

Thanks so much for your help Marcelo!
This is perfect. I'm now trying to make a new column called Error and just saying yes in the rows that contain an error, instead of doing the highlighting. Don't do it for me, but could you tell me how I can print "yes" in x.offset(,5) in the error rows? Thanks so much, first day doing VBA.

 

[shutterback]

Welcome to Mr Excel

Maybe this
You didn't say where you want the results, so i assumed results in column E.

Sub counter()
    Dim x As Range, counter As Long
    
    counter = 0
    For Each x In Range("D2", Range("D" & Rows.Count).End(xlUp))
        If x.Offset(0, -1) = "Buy" Then
            counter = counter + x
        ElseIf x.Offset(0, -1).Value Like "*Sell*" Then
            counter = counter - x
            If InStr(1, x.Offset(, -1), "long", vbTextCompare) And counter < 0 Then
                x.Offset(, 1).Interior.Color = vbRed
            ElseIf InStr(1, x.Offset(, -1), "short", vbTextCompare) And counter > 0 Then
                x.Offset(, 1).Interior.Color = vbRed
            End If
        End If
        x.Offset(, 1) = counter
    Next x
End Sub

Hope this helps

M.

Thanks so much for your help Marcelo!
This is perfect. I'm now trying to make a new column called Error and just saying yes in the rows that contain an error, instead of doing the highlighting. Don't do it for me, but could you tell me how I can print "yes" in x.offset(,5) in the error rows? Thanks so much, first day doing VBA.

 

[Marcelo Branco]

Thanks so much for your help Marcelo!
This is perfect. I'm now trying to make a new column called Error and just saying yes in the rows that contain an error, instead of doing the highlighting. Don't do it for me, but could you tell me how I can print "yes" in x.offset(,5) in the error rows? Thanks so much, first day doing VBA.

Maybe...
Instead of
x.Offset(, 1).Interior.Color = vbRed

try
x.Offset(, 5).Value = "Yes"

M.