作者balalala10 (Andy)
看板Office
标题[算表] VBA IF 判断条件
时间Sat Mar 31 20:15:46 2018
大家好,我想要在VBA中使用IF判断条件,
因为用函数好像没办法串联。
条件如下:
suggest push in = A>0, B<0
suggest push out= A>0, B>0
no demand suggest push out= A=0, C or D or E>0
Excel 函数如下:
=IF(AND(A2>0,B2<0),"suggest push in","")
=IF(AND(A2>0,B2>0),"suggest push out","")
=IF(AND(A4=0,OR(C4>0,D4>0,E4>0)),"no demand sugggest push in","")
表格图片如下:
[img]
https://imgur.com/lLtFRGK[img]
_______________________________________________________________________
VBA code如下:
Sub test()
lastrow = Range("F" & Rows.Count).End(xlUp).Row
For Each cell In Range("F2:F" & lastrow)
A = "A2: A"
B = "B2: B"
C = "C2: C"
D = "D2: D"
E = "E2: E"
If A > 0 & B > 0 Then
cell.Value = "suggest push out"
ElseIf A > 0 & B < 0 Then
cell.Value = "suggest push in"
ElseIf (C > 0 Or D > 0 Or E > 0) And A = 0 Then
cell.Value = "no demand sugggest push in"
End If
Next
End Sub
_______________________________________________________________________
跑後只有push in的result出现在F1,F2,不晓得是范围抓错还是定义有误。
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 36.228.148.115
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1522498549.A.F60.html
※ 编辑: balalala10 (36.228.148.115), 03/31/2018 20:19:44
2F:→ soyoso: 公式的话如a栏只有正数和0值的话 03/31 20:48
4F:→ soyoso: c:e栏只会有正数可用sum,如可能有负数或0值的话用countif 03/31 20:49
5F:→ soyoso: 如a栏也会有负数,而要指定0值才为真的话,请再自行加入 03/31 20:49
6F:→ soyoso: a栏的判断(如a2=0) 03/31 20:50