-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")
-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")
0
The use of that formula in .formula
implies adjusting the style to English, which means:
;
for ,
SE
passes to IF
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.
conditional
se
useRange
:If Range(“J5”) = "OK" Then .....
– Ricardo Pontual