1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by MSR, Jul 11, 2018.

  1. MSR

    MSR New Member

    Messages:
    4
    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

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    331
    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.
  3. MSR

    MSR New Member

    Messages:
    4
    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.
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    331
    Based upon your explanation, here is how I would approach your issue.
    Code (vb):

    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
    Thomas Kuriakose and MSR like this.
  5. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !

    Without any loop,

    • as a very beginner starter :​
    Code (vb):
    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 (vb):
    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 !
    Thomas Kuriakose and MSR like this.
  6. MSR

    MSR New Member

    Messages:
    4

    Thank you Alan
  7. MSR

    MSR New Member

    Messages:
    4
    Thank you Marc

Share This Page