Unable to calculate Profit/Loss using VBA code

[MSR]

Hello,

I'm new to VBA and trying to learn.

I have written an Loop code to find profit/loss, however the result always shows loss.

Could you please take a look at the attachment and tell me what is wrong in my code? Thanks,

Sub ProfitLossLoop ()
Module ProfitLoss

 

[AlanSidman]

I have looked at your spreadsheet and your code and I really have no idea what you are trying to accomplish. Please explain in simple English what you are trying to do and what the expected results should be. Based upon that we will develop some working code for you. Be as specific as possible using the steps you would use if you were doing this manually.

 

[MSR]

Hello Alan,

Thanks for your reply,

I want VBA to look at the
1. value in V (in RawData sheet),
2. if it is greater than 0, the result should be shown as Profit in column Y
3. if it is negative, the result should show as Loss in Colomn Y

My VBA code is in module name ProfitLoss

Here I want to understand what in VBA coding I'm doing wrong which shows the result as loss irrespective of what I put in my VBA code. This is just sample data and I want to learn excel vba.

Thanks once again for taking time.

 

[AlanSidman]

Based upon your explanation, here is how I would approach your issue.

Option Explicit

Sub PnL()
    Dim i As Long, lr As Long
    'Find the last cell used in column V
    lr = Range("V" & Rows.Count).End(xlUp).Row
    'Remove screen flickering
    Application.ScreenUpdating = False
    'Set loop to look at values in column V
    For i = 2 To lr
        If Range("V" & i) > 0 Then
            Range("Y" & i) = "Profit"
        Else: Range("Y" & i) = "Loss"
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub

 

[Marc L]

Hi !

Without any loop,

• as a very beginner starter :​

Sub Demo1()
    With wsRawData.ListObjects(1).DataBodyRange.Columns("V").Offset(, 3)
        .Formula = "=IF(V2>0,""Profit"",""Loss"")"
        .Formula = .Value
    End With
End Sub

• As a beginner starter :​

Sub Demo2()
    With wsRawData.[A1].ListObject.DataBodyRange.Columns("V")
        .Offset(, 3).Value = .Parent.Evaluate("IF(" & .Address & ">0,""Profit"",""Loss"")")
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[MSR]

Based upon your explanation, here is how I would approach your issue.

Option Explicit

Sub PnL()
    Dim i As Long, lr As Long
    'Find the last cell used in column V
    lr = Range("V" & Rows.Count).End(xlUp).Row
    'Remove screen flickering
    Application.ScreenUpdating = False
    'Set loop to look at values in column V
    For i = 2 To lr
        If Range("V" & i) > 0 Then
            Range("Y" & i) = "Profit"
        Else: Range("Y" & i) = "Loss"
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub

Thank you Alan

 

[MSR]

Hi !

Without any loop,

• as a very beginner starter :​

Sub Demo1()
    With wsRawData.ListObjects(1).DataBodyRange.Columns("V").Offset(, 3)
        .Formula = "=IF(V2>0,""Profit"",""Loss"")"
        .Formula = .Value
    End With
End Sub

• As a beginner starter :​

Sub Demo2()
    With wsRawData.[A1].ListObject.DataBodyRange.Columns("V")
        .Offset(, 3).Value = .Parent.Evaluate("IF(" & .Address & ">0,""Profit"",""Loss"")")
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Thank you Marc