vba 使用具有多个条件的 If 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6306159/
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
Use of If statement with multiple conditions
提问by mehak
I have written the following code which is basically supposed colour some boxes accordingly. Whenever i run this code, it runs the first case i.e. even when some other case is required to be selected. here is the code.
我已经编写了以下代码，它基本上应该相应地为一些框着色。每当我运行此代码时，它都会运行第一个案例，即即使需要选择其他案例。这是代码。
Sub Macro_quaterly()
If Sheet2.Range("B6").Value = 1 Or 2 Or 3 Then
Range("D7").Select
With Selection.Interior
'.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
Sheet2.Cells(6, 11) = "rrrrrrr"
End With
ElseIf Sheet2.Range("B6").Value = 4 Or 5 Or 6 Or 7 Then
Range("D7:E7").Select
With Selection.Interior
'.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
Sheet2.Cells(6, 12) = "rddddddr"
End With
ElseIf Sheet2.Cells(6, 2) = 8 Or 9 Or 10 Or 11 Then
Range("D7:F7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 12 Or 13 Or 14 Or 15 Then
Range("D7:G7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 16 Or 17 Or 18 Or 19 Then
Range("D7:H7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 20 Or 21 Or 22 Or 23 Then
Range("D7:I7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 24 Or 25 Or 26 Or 27 Then
Range("D7:J7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 28 Or 29 Or 30 Or 31 Then
Range("D7:K7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 32 Or 33 Or 34 Or 35 Then
Range("D7:L7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 36 Or 37 Or 38 Or 39 Then
Range("D7:M7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 40 Or 41 Or 42 Or 43 Then
Range("D7:N7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 44 Or 45 Or 46 Or 47 Then
Range("D7:O7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 48 Or 49 Or 50 Or 51 Then
Range("D7:P7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 52 Or 53 Or 54 Or 55 Then
Range("D7:Q7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 56 Or 57 Or 58 Or 59 Then
Range("D7:R7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Sheet2.Cells(6, 2) = 60 Then
Range("D7:S7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub
Your help would be appreciated.
您的帮助将不胜感激。
回答by mwolfe02
An alternative is to use Select..Casestatements. I think it is a lot more readable for this kind of thing:
另一种方法是使用Select..Case语句。我认为这种事情更具可读性：
Select Case Sheet2.Range("B6").Value
Case 1, 2, 3
Range("D7").Select
With Selection.Interior
'.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
Sheet2.Cells(6, 11) = "rrrrrrr"
End With
Case 4, 5, 6, 7
Range("D7:E7").Select
With Selection.Interior
'.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
Sheet2.Cells(6, 12) = "rddddddr"
End With
Case ....
....
Case Else
....
End Select
回答by jonsca
If Sheet2.Range("B6").Value = 1 Or 2 Or 3 Then
This line is not doing what you think it is doing. You need to put If Sheet2.Range("B6").Value = 1 Or Sheet2.Range("B6").Value = 2 Or Sheet2.Range("B6").Value = 3 Or Sheet2.Range("B6").Value = 4 Then(or substitute an intermediate variable in for Sheet2.Range("B6").Value)
这条线没有做你认为它在做的事情。您需要放入If Sheet2.Range("B6").Value = 1 Or Sheet2.Range("B6").Value = 2 Or Sheet2.Range("B6").Value = 3 Or Sheet2.Range("B6").Value = 4 Then（或在 for 中替换中间变量Sheet2.Range("B6").Value）
回答by Jean-Fran?ois Corbett
The answer to your problem lies in the fact that the numbers in your Orconditions are implicitly coerced to Boolean values, and that when this happens, everything except 0 is coerced to True. To convince yourself of this, try Debug.Print CBool(13)and Debug.Print CBool(0).
您的问题的答案在于，您的Or条件中的数字被隐式强制为布尔值，并且当发生这种情况时，除 0 之外的所有内容都被强制为True. 要说服自己这一点，请尝试Debug.Print CBool(13)和Debug.Print CBool(0)。
I am a bit peeved that none of the people who have posted previous answers have explained this, hence this post which might otherwise have been considered repetitive!
我有点生气，因为之前发布过答案的人都没有解释过这一点，因此这篇文章可能会被认为是重复的！
Instead of
代替
If Sheet2.Range("B6").Value = 1 Or 2 Or 3 Then
use
用
If Sheet2.Range("B6").Value = 1 Or _
Sheet2.Range("B6").Value = 2 Or _
Sheet2.Range("B6").Value = 3 Then
etc. Or, even better, a Select Caseconstruct as suggested by @mwolfe02.
等等。或者，更好的Select Case是，@mwolfe02 建议的构造。
回答by Jean-Fran?ois Corbett
In addition to the errors noted by the other answers posted here, it is important to note the construct used for testing your condition is Ifwith ElseIf. By using this to test your condition, you will always execute the first ElseIfcondition resolving to Trueand skip any later conditions which may be defined.
除了此处发布的其他答案指出的错误之外，重要的是要注意用于测试您的条件的构造是Ifwith ElseIf。通过使用它来测试您的条件，您将始终执行ElseIf解析为的第一个条件True并跳过可能定义的任何后续条件。
This means you may end up with logical issues in formatting according to the appropriate conditions you intended to format.
这意味着根据您打算格式化的适当条件，您最终可能会遇到格式化的逻辑问题。
For this reason, I would recommend using the Casestatement construct as provided by @mwolfe02 in the response above along with all similar formatting conditions within the same Casestatement. This would prevent various formatting situations from occurring based on what conditions had been met in a particular ordering of data.
出于这个原因，我建议使用Case@mwolfe02 在上面的响应中提供的语句构造以及同一Case语句中的所有类似格式条件。这将防止根据特定数据顺序中满足的条件发生各种格式化情况。
Hope that helps.
希望有帮助。
回答by diagonalbatman
Ok, So the problem here is the "If statement".
好的，所以这里的问题是“If 语句”。
The correct way of defining the 'OR' is as so
定义“OR”的正确方法是这样
If Sheet2.Range("B6").Value = 1 Or Sheet2.Range("B6").Value = 2 Or Sheet2.Range("B6").Value = 3 Then
回答by Reafidy
Lots of redundant code you can get rid of and as mentioned a couple of times go with select case.
您可以删除许多冗余代码，并且如前所述，有几次选择案例。
Try:
尝试：
Sub Macro_quaterly()
Dim rCell As Range
Select Case Sheet2.Range("B6").Value
Case 1, 2, 3
Set rCell = Range("D7")
Sheet2.Cells(6, 11) = "rrrrrrr"
Case 4, 5, 6, 7
Set rCell = Range("D7:E7")
Sheet2.Cells(6, 12) = "rddddddr"
Case 8, 9, 10, 11
Set rCell = Range("D7:F7")
Case Else
End Select
With rCell.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Set rCell = Nothing
End Sub
