VBA: Calculating the value at every row, but formula changes depending on the value in one column

[yijiyap]

I would like to create a simple formula to calculate the net change on a certain row, and print it in the same row (column H). But what I plug into the formula will depend on the value at each row's column A.

This is the code that I tried:

Sub totalPnL()
    Dim LR As Long
    LR = Range("D" & Rows.Count).End(xlUp).Row
    
    If Range("A2:A" & LR).Value = "WIN" Then
        Range("H2:H" & LR).Formula = "=ABS(D2-F2)*G2"

    ElseIf Range("A2:A" & LR).Value = "LOSS" Then
        Range("H2:H" & LR).Formula = "=-ABS(D2-E2)*G2"
    
    End If
        
    Range("T2") = Application.WorksheetFunction.Sum(Range("H:H"))

End Sub

I feel something is wrong with the If statement, but I'm not sure how to edit it

Thanks for all the help

 

[Peter_SSs]

Welcome to the MrExcel board!

I am assuming that all the values in A2:A & LR are either "WIN" or "LOSS"
If that is the case, then try this code.

Sub totalPnL_v2()
  Dim LR As Long
  
  LR = Range("D" & Rows.Count).End(xlUp).Row
  Range("H2:H" & LR).Formula = "=IF(A2=""WIN"",ABS(D2-F2)*G2,-ABS(D2-E2)*G2)"
End Sub

 

[yijiyap]

Wow thanks for the well-wishes and the prompt answer I didn't think of that logic haha It worked like a charm! Cheers

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.