Excel, analyze condition in data table

Question

I need to change one column of values in excel according to the condition of another column. For example, analyze tax ratings and if they are "sales returns" I need to change the quantity to negative. How to do?

Answer 1

As I said, despite the good solution with function given by @Leandroluk, I decided to make code for a button, that put below. In this code I change directly the field "quantity" and tax values for "return" Nfs, sales remain positive values (I always analyze the whole table so I can insert new lines):

Private Sub bDEVOL_Click()

Dim DEV As Worksheet
Set DEV = ThisWorkbook.Sheets("DEVOLUCOES")

Dim vlQTD As String, vlTOT As String, vlICM As String, vlPIS As String, vlCOFINS As String, vlIPI As String
Dim Lin As Variant, UltLin As Long

With DEV
    UltLin = .Cells(.Rows.Count, 1).End(xlUp).Row
    For Lin = 3 To UltLin
        vlQTD = .Cells(Lin, "G").Value 'quantidade
        vlTOT = .Cells(Lin, "H").Value 'Vlr total da NF
        vlIPI = .Cells(Lin, "J").Value 'Vlr IPI
        vlICM = .Cells(Lin, "K").Value 'Vlr ICM
        vlPIS = .Cells(Lin, "O").Value 'Vlr PIS
        vlCOFINS = .Cells(Lin, "M").Value 'Vlr COFINS
        If InStr("1201,1202,1410,1411,2201,2410,2411,3201", .Cells(Lin, "Q").Value) > 0 Then 'Class. Fiscais de devolução
            If .Cells(Lin, "G").Value > 0 Then 'se já estiver negativo, ignora para não tornar positivo
                .Cells(Lin, "G").Value = vlQTD * -1
                .Cells(Lin, "H").Value = vlTOT * -1
                .Cells(Lin, "J").Value = vlIPI * -1
                .Cells(Lin, "K").Value = vlICM * -1
                .Cells(Lin, "O").Value = vlPIS * -1
                .Cells(Lin, "M").Value = vlCOFINS * -1
            End If
        End If
    Next Lin
End With

End Sub

Answer 2

Well theoretically, you would have a column with selective type values (single values within a list) and from these values you should do a treatment right? then I think you should have the following solution:

Unique values for example:

• 1 [Stay positive]
• 2 [Goes negative]
• 3 [ignores and Zera the value]

Example table

  |    A    | B | C |
  | ------- |---|---|
1 | 100.00  | 1 | ? |
2 | 200.50  | 3 | ? |
3 | 123.45  | 2 | ? |

In order for me to satisfy column "C", using the rule listed above, in each cell of column C I would have something like this:

=SE(B1=1;+A1;SE(B1=2;-A1;SE(B1=3;0;A1)))

This function makes the conditional validation of the data in the following structure:

if(B1 == 1) {
    return +A1; /* o valor inicial como positivo */
} else {
    if(B1 == 2) {
        return +A1; /* o valor inicial como negativo */            
    } else {
        if(B1 == 3) { 
            return 0; /* ignora os valores e define um especifico*/
        } else {
            return A1; /* caso nao atenda nada, retorne o valor inicial*/
        }
    }
}