VBA 中的 If 和 Or 多条语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29033649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If And Or Multiple Statements in VBA
提问by Helfenstein
I want redistribute an Excel file with 14 Columns to the correct Column (with 12.000 rows).
我想将一个有 14 列的 Excel 文件重新分配到正确的列（有 12.000 行）。
For this I have to use some If And Or Statements to put the numbers in a matrix. But apparently I don't get the right things out of it.
为此，我必须使用一些 If And Or 语句将数字放入矩阵中。但显然我没有从中得到正确的东西。
It makes all my cells zero, while the cells with a value should keep the value.
它使我的所有单元格为零，而具有值的单元格应保留该值。
Where do I go wrong?:
我哪里出错了？：
For i = 1 To LastRow
If Cells(i, 8).Value2 = "" Then Cells(i, 8).Value2 = 0
If Cells(i, 1).Value2 < 437 And Cells(i, 5).Value2 = "aa" _
Or Cells(i, 5).Value2 = "bb" _
Or Cells(i, 5).Value2 = "cc" _
Or Cells(i, 5).Value2 = "dd" _
Or Cells(i, 5).Value2 = "ee" _
Or Cells(i, 5).Value2 = "ff" _
Or Cells(i, 5).Value2 = "gg" _
And Cells(i, 7).Value2 = "" _
Then Cells(i, 7).Value2 = 0
Next i
So if the cell contains an aa or bb or cc or dd or ee or ff or gg and is empthy the cell should become 0 otherwise it should stay the same value. After that it should go into a matrix
因此，如果单元格包含 aa 或 bb 或 cc 或 dd 或 ee 或 ff 或 gg 并且为空单元格应变为 0 否则应保持相同的值。之后它应该进入一个矩阵
Then Matrixgetallen(i, 2) = CDbl(Cells(i, 7).Value2)
But I didn't manage to get that in the same if statement.
但是我没有设法在同一个 if 语句中得到它。
If have 6 of these kind of If-statements, so probably If Then Else doesn't work.
如果有 6 个这样的 If 语句，那么 If Then Else 可能不起作用。
回答by brettdj
Hard to optimise without seeing your full code but for this portion:
很难在没有看到完整代码的情况下进行优化，但对于这一部分：
- Break the
ANDinto twoIFs as VBA doesn't short circuit - Rather than a long sequence of
ORs, do a single shot test against an array (a numeric result means the exact string is found)
- 由于 VBA不会短路，因此将其
AND分成两个IFs OR对数组进行单次测试，而不是一长串s（数字结果表示找到了确切的字符串）
code
代码
i = 1
Dim strIN
strIN = Array("aaf", "bb", "cc", "dd", "ee", "ff", "gg")
If Cells(i, 1).Value2 < 437 Then
If Len(Cells(i, 5)) = 0 Then
Cells(i, 7).Value2 = 0
Else
If IsNumeric(Application.Match(Cells(i, 5), strIN, 0)) Then Cells(i, 7).Value2 = 0
End If
End If
回答by Helfenstein
Thank you for the hint Axel. I never have seen this before, but it works great! This did the trick:
谢谢你的提示阿克塞尔。我以前从未见过这个，但效果很好！这做到了：
If Cells(i, 1).Value2 < 437 And (Cells(i, 5).Value2 = "aa" _
Or Cells(i, 5).Value2 = "bb" _
Or Cells(i, 5).Value2 = "cc" _
Or Cells(i, 5).Value2 = "dd" _
Or Cells(i, 5).Value2 = "ee" _
Or Cells(i, 5).Value2 = "ff" _
Or Cells(i, 5).Value2 = "gg") _
And Cells(i, 7).Value2 = "" _
Then Cells(i, 7).Value2 = 0
But I also really like the answers of Brett . That is a nice new way to approach it.
但我也很喜欢布雷特的回答。这是一种很好的新方法。
