How do I write this formula in . formula?

Asked

Viewed 41 times

-2

How I write the formula below in . formula in vba?

=SE(J5="OK";"Realizado";SE(E(J5="NOK";I5>0);"Não realizado";SE( E(J5="NOK";I5<0); "Não realizado")

vba

  • conditional se use Range: If Range(“J5”) = "OK" Then .....

1 answer

0

The use of that formula in .formula implies adjusting the style to English, which means:

  • Barter ; for ,
  • Change function names to English. Ex: SE passes to IF
  • Escape all double quotes inside the formula by placing two in a row

The example you have would look like this:

ActiveCell.formula = "=IF(J5=""OK"",""Realizado"",IF(AND(J5=""NOK"",I5>0),""Não realizado"",IF(AND(J5=""NOK"",I5<0),""Não realizado"")))"

I put as target cell to the current cell with ActiveCell, but can adjust to the one that suits, with the Range.

Personally I think it is simpler to use normal VBA code, as if it were normal programming, and to do so:

If Range("J5").Value = "OK" Then
    ActiveCell.Value = "Realizado"
ElseIf Range("J5").Value = "NOK" And Range("I5").Value > 0 Then
    ActiveCell.Value = "Não Realizado"
ElseIf Range("J6").Value = "NOK" And Range("I5").Value < 0 Then
    ActiveCell.Value = "Não Realizado"
End If

Despite all this is not a formula and so will not adjust based on the changes of the remaining values, and so may not serve for your goal.

Now, if you notice, you have two equal roles in ElseIf, which does not make much sense this construction and is something that has to review.

For what is written, just put "Realizado" when J5 has the value "Ok" and "Não Realizado" when J5 has "NOK" representing the same. The following is sufficient:

If Range("J5").Value = "OK" Then
    ActiveCell.Value = "Realizado"
ElseIf Range("J5").Value = "NOK" Then
    ActiveCell.Value = "Não Realizado"
End If

In the example of .formula the same principle applies, and the same simplification in that case would be:

ActiveCell.formula = "=IF(J5=""OK"",""Realizado"",IF(AND(J5=""NOK""),""Não realizado""))"

Browser other questions tagged vba

You are not signed in. Login or sign up in order to post.