• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to calculate Profit/Loss using VBA code

MSR

New Member
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
 

Attachments

  • WiseOwlVBA.xlsm
    195.1 KB · Views: 10
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.
 
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.
 
Based upon your explanation, here is how I would approach your issue.
Code:
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
 
Hi !

Without any loop,

• as a very beginner starter :​
Code:
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 :​
Code:
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 !
 
Based upon your explanation, here is how I would approach your issue.
Code:
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
 
Hi !

Without any loop,

• as a very beginner starter :​
Code:
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 :​
Code:
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
 
Back
Top