当前位置： 文江博客话题详情

使用具有多个条件的 If 语句

发布于 2024-11-14 23:52:28 字数 4474 浏览 3 评论 0原文

我编写了以下代码，基本上应该相应地为一些框着色。每当我运行此代码时，它都会运行第一种情况，即即使需要选择其他情况也是如此。这是代码。

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

我们将不胜感激您的帮助。

原文

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.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（6）

空心↖ 2024-11-21 23:52:28

另一种方法是使用 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

An alternative is to use Select..Case statements. I think it is a lot more readable for this kind of thing:

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
回复 原文
So尛奶瓶 2024-11-21 23:52:28
 If Sheet2.Range("B6").Value = 1 Or 2 Or 3 Then

这条线没有做你认为它正在做的事情。您需要输入 If Sheet2.Range("B6").Value = 1 Or Sheet2.Range("B6").Value = 2 Or Sheet2.Range("B6").Value = 3 Or Sheet2.Range ("B6").Value = 4 然后（或用中间变量替换 Sheet2.Range("B6").Value）

 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)

回复 原文
月隐月明月朦胧 2024-11-21 23:52:28

问题的答案在于，Or 条件中的数字被隐式强制为布尔值，并且当发生这种情况时，除 0 之外的所有内容都被强制为 True 。要说服自己相信这一点，请尝试 Debug.Print CBool​​(13) 和 Debug.Print CBool​​(0)。

我有点恼火的是，之前发布答案的人都没有解释这一点，因此这篇文章可能会被认为是重复的！

而不是

If Sheet2.Range("B6").Value = 1 Or 2 Or 3 Then

使用

If Sheet2.Range("B6").Value = 1 Or _
    Sheet2.Range("B6").Value = 2 Or _
    Sheet2.Range("B6").Value = 3 Then

等。或者，更好的是，使用 @mwolfe02 建议的 Select Case 构造。

The answer to your problem lies in the fact that the numbers in your Or conditions 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).

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 Case construct as suggested by @mwolfe02.

回复 原文
挖个坑埋了你 2024-11-21 23:52:28

好的，
所以这里的问题是“If语句”。

定义“OR”的正确方法如下

If Sheet2.Range("B6").Value = 1 Or Sheet2.Range("B6").Value = 2 Or Sheet2.Range("B6").Value = 3 Then

Ok,
So the problem here is the "If statement".

The correct way of defining the 'OR' is as so

If Sheet2.Range("B6").Value = 1 Or Sheet2.Range("B6").Value = 2 Or Sheet2.Range("B6").Value = 3 Then
回复 原文
冷情 2024-11-21 23:52:28

除了此处发布的其他答案指出的错误之外，重要的是要注意用于测试条件的构造是 If 和 ElseIf。通过使用它来测试您的条件，您将始终执行解析为 True 的第一个 ElseIf 条件，并跳过可能定义的任何后续条件。

这意味着根据您想要格式化的适当条件，您最终可能会在格式化时遇到逻辑问题。

因此，我建议使用上述响应中 @mwolfe02 提供的 Case 语句构造以及同一 Case 语句中的所有类似格式条件。这将防止根据特定数据排序中满足的条件而发生各种格式化情况。

希望有帮助。

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 If with ElseIf. By using this to test your condition, you will always execute the first ElseIf condition resolving to True and skip any later conditions which may be defined.

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 Case statement construct as provided by @mwolfe02 in the response above along with all similar formatting conditions within the same Case statement. This would prevent various formatting situations from occurring based on what conditions had been met in a particular ordering of data.

Hope that helps.

回复 原文
折戟 2024-11-21 23:52:28

您可以删除许多冗余代码，并且正如多次提到的，选择案例。

尝试：

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

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
回复 原文
~没有更多了~

关于作者

独享拥抱

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

寻梦旅人

文章 0 评论 0

冰美式不加糖

文章 0 评论 0

m0_51416705

文章 0 评论 0

123456wqwqwq

文章 0 评论 0

qq_R47skh

文章 0 评论 0

hs1283

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文