Excel if语句缩短了吗？ [英] Excel if statement shorten down?

查看：145
本文介绍了Excel if语句缩短了吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我根据不同单元格中的值将VBA代码写入彩色单元格。是否有可能缩短以下时间？我不熟悉VBA中的脚本，并且希望如果可以缩短它，那么我可以将它应用到更大的文件中。

I have VBA code written to color cells based on what value is in a different cell. Would it be possible to shorten the following down? I'm new to scripting in VBA and was hoping that if this can be shortened then I could apply it to a bigger file.

 Sub RoutingCheck()
   Dim I As Long, r1 As Range, r2 As Range

   For I = 2 To 456
      Set r1 = Range("A" & I)
      Set r2 = Range("B" & I)

    If r1.Value = 94 And r2.Value = " " Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -99 Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = " " Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -99 Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value <> "" And r2.Value <> -99 And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> " " Then r1.Interior.Color = vbGreen
    If r1.Value = 94 And r2.Value <> "" And r2.Value <> -99 And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> " " Then r2.Interior.Color = vbGreen

    If r1.Value = 1 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 1 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 2 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 2 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 3 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 3 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 4 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 4 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 5 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 5 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 6 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 6 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 7 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 7 And r2.Value = -99 Then r2.Interior.Color = vbGreen

       Next I
       'Error

        End Sub


推荐答案

拿第一个区块，你可能会这样做：

Taking the first block, you might do something like this:

If r1.Value = 94 Then
   Select case r2.Value
      case " ", "", -99, -66, -77
         r1.Interior.Color = vbRed
         r2.Interior.Color = vbRed
      Case else
         r1.Interior.Color = vbGreen
         r2.Interior.Color = vbGreen
    End Select
ElseIf r1.value = 1 then
   ' repeat the select case or use a function to return the correct colour.
' repeat
End If

你的其他街区似乎基本上只关心r2是-99，在这种情况下使用绿色，否则为红色。

Your other blocks appear to basically only care if r2 is -99, in which case use green, otherwise red.

这篇关于Excel if语句缩短了吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆