VBA: Calculating the value at every row, but variables change depending on one column-CodePudding

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

CodePudding user response：

As written in the comment a formula will be easier and quicker but if you want to have your code "fixed" then the following code would do it.

Sub totalPnL()
    Dim LR As Long, i As Long
    
    LR = Range("D" & Rows.Count).End(xlUp).Row
    
    
        ' the original statement
        ' Range("A2:A" & LR).Value = "WIN"
        ' cannot work as Range("A2:A" & LR) will be an array in case LR > 2
        ' in this case you have to loop
        ' AGAIN: usage of a formula will be better
    
        For i = 2 To LR
            If Range("A" & i).Value = "WIN" Then
                Range("H" & i).FormulaR1C1 = "=ABS(RC[-4]-RC[-2])*RC[-1]"

            ElseIf Range("A" & i).Value = "LOSS" Then
                Range("H" & i).FormulaR1C1 = "=ABS(RC[-4]-RC[-2])*RC[-1]"
    
            End If
        Next i
        
    Range("T2") = Application.WorksheetFunction.Sum(Range("H:H"))

End Sub

To make the code easier to read you could use a Select statement

For i = 2 To LR
    Select Case Range("A" & i).Value
        Case "WIN"
            Range("H" & i).FormulaR1C1 = "=ABS(RC[-4]-RC[-2])*RC[-1]"
        Case "LOSS"
            Range("H" & i).FormulaR1C1 = "=ABS(RC[-4]-RC[-2])*RC[-1]"
    End Select
Next i

Another formula you could use would be =IF(A2="WIN";1;(IF(A2="LOSS";-1)))*ABS(D2-F2)*G2