[已解决]执行代码出现以下异常，请老师帮忙看下

ruhong18

Sub 条件填充颜色()
Dim a
For i = 2 To 500
  Range("L2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()-RC[-11]"
    Range("L2").Select
    Selection.NumberFormatLocal = "G/通用格式"
    Selection.AutoFill Destination:=Range("L2:L500"), Type:=xlFillDefault
    Range("L2:L500").Select
    ActiveWindow.SmallScroll Down:=-168
a = Sheet1.Range("l" & i) '根据H列的取值范围
If a >= 20 Then Sheet1.Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 3
If (a >= 15 And a < 20) Then Sheet1.Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 44
If (a >= 7 And a < 15) Then Sheet1.Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 36
If a < 7 Then Sheet1.Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 34
Next i
Range("A1:I1").Select
    With Selection.Font
        .Name = "微软雅黑"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("A1:I1").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "微软雅黑"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub

点击调试 就出现黄色部分If a >= 20 Then

最佳答案

月排行榜 / 总排行榜

hfwufanhf2006

2021-6-29 09:12

ruhong18 发表于 2021-6-28 19:52
附件如上，该如何修改呢？

我感觉改好了，基本维持了你原来的框架，有几点变化：
1、L列原来是采用工作表函数来填充，我换成了Vba函数，因此在L列有数字但看不到公式了；
2、循环没必要到500行，因为后面大部分是空行，这也是你红色标注的那部分。原来出错误的地方就是因为“合计”行不能参与计算，导致L列的结果是error 255这类结果，当然也就不能据此填充颜色了。我把循环改成了 [a500].end(3).row-1，[a500].end(3).row是从单元格a500往上找的第一行，也可以看成是最末行，就是合计行，-1表示排除了合计行。如果你的最大数据源超过500行，这里的[a500]要相应增加，可以写成[a65000]，表示从a65000往上找最末行；
3、vba是可以盲操作的，因此不需要.select，录制宏会有.select，因为录制宏是完全模仿人工操作，因此每一步都有对应的.select，这些代码我都删掉了；
4、单元格引用有两种写法，你原代码用的是range格式，我改成了cells，这两个是通用的，功能没差别，习惯而已；
5、最后是关于格式定义，我记得格式定义需要在数据录入之前定义才能有效果，如果先输入了数据后改变格式，格式仍会保留原先的不变。所以我把"G/通用格式"的定义放在了最前面；

跳转到最佳答案楼层

hfwufanhf2006

有很多可能是单元格含有文本型数字，一种是定义为文本的数字，可能性比较小一些；另一种常见的错误是含有空格，空格看不见，肯定也是文本；
可以用函数转换，写成 if val(a)>=20 then 这种形式，空格会被转换成 0；

ruhong18

hfwufanhf2006 发表于 2021-6-28 10:59
有很多可能是单元格含有文本型数字，一种是定义为文本的数字，可能性比较小一些；另一种常见的错误是含有空 ...

你好，是的，L列我用了辅助列，如何根据VBA代码执行后的行数，对应的生成辅助列，同步其对应行数？

hfwufanhf2006

ruhong18 发表于 2021-6-28 11:23
你好，是的，L列我用了辅助列，如何根据VBA代码执行后的行数，对应的生成辅助列，同步其对应行数？

  没看懂，可能你需要上传一个实际的例子才好理解。

ruhong18

hfwufanhf2006 发表于 2021-6-28 16:45
没看懂，可能你需要上传一个实际的例子才好理解。

附件如上，该如何修改呢？

hfwufanhf2006

ruhong18 发表于 2021-6-28 19:52
附件如上，该如何修改呢？

我感觉改好了，基本维持了你原来的框架，有几点变化：
1、L列原来是采用工作表函数来填充，我换成了Vba函数，因此在L列有数字但看不到公式了；
2、循环没必要到500行，因为后面大部分是空行，这也是你红色标注的那部分。原来出错误的地方就是因为“合计”行不能参与计算，导致L列的结果是error 255这类结果，当然也就不能据此填充颜色了。我把循环改成了 [a500].end(3).row-1，[a500].end(3).row是从单元格a500往上找的第一行，也可以看成是最末行，就是合计行，-1表示排除了合计行。如果你的最大数据源超过500行，这里的[a500]要相应增加，可以写成[a65000]，表示从a65000往上找最末行；
3、vba是可以盲操作的，因此不需要.select，录制宏会有.select，因为录制宏是完全模仿人工操作，因此每一步都有对应的.select，这些代码我都删掉了；
4、单元格引用有两种写法，你原代码用的是range格式，我改成了cells，这两个是通用的，功能没差别，习惯而已；
5、最后是关于格式定义，我记得格式定义需要在数据录入之前定义才能有效果，如果先输入了数据后改变格式，格式仍会保留原先的不变。所以我把"G/通用格式"的定义放在了最前面；

ruhong18

hfwufanhf2006 发表于 2021-6-29 09:12
我感觉改好了，基本维持了你原来的框架，有几点变化：
1、L列原来是采用工作表函数来填充，我换成了Vba ...

你好，老师：就这个辅助列的问题，我想延伸再学点东西如下：另外有个表格想要G列的值，等于D列除以C列，结果保留一位小数点，求平均值；
Dim a
For i = 2 To [a500].End(3).Row - 1
    Cells(i, "g").NumberFormatLocal = "G/通用格式"
    Cells(i, "g") = Int(Now - Cells(i, 1))
    a = Cells(i, "g")

next i


以上这段代码改怎么修改呢？
cells(f,7)=cells(f,4)/cells(f,3)   或    Cells(i, "g") =  Cells(i, "d") / Cells(i, "C")    好像都不行？

hfwufanhf2006

ruhong18 发表于 2021-6-29 11:31
你好，老师：就这个辅助列的问题，我想延伸再学点东西如下：另外有个表格想要G列的值，等于D列除以C列， ...

1、要先了解cells和range的写法不同：
     cells(x,y)：x就是行坐标，y是列坐标，跟数学坐标是一致的，所以搭配循环很好用；
     range("a5")：a是列，5是行，其实与cells是相反的。主要是range在引用字母列的时候不好直接判断，尤其是在涉及行和列的双循环中，比如第5列要判断为E列，这个就需要有点其他的技巧了。但cells就不需要，直接cells(x,5)或者cells(x,"e")都是可以的；

     正因为如此，excel允许这两者结合使用。比如range("a5:h20")表示a5:h20这个区域，也可以写成range(cells(5,1),cells(20,8))，其中cells(5,1),是左上角的起始单元格坐标a5（第5行第1列），cells(20,8)是右下角的终点坐标h20（第20行第8列，太后面的列不好数，也可以用字母代替），它抛开了字母的限制，完全坐标化了，这在循环中搭配双循环参数就非常简单了。
    不过range("a5")只表示一个单元格a5，应该是不能写成range(cells(5,1))的，由此可以看出，range通常适合引用一个坐标点固定另一个是变化的情况，比如range("a5:h" & [h65000].end(3).row)，这里只有一个变量是H列的行数不知道，但左上角a5确是固定的才好这么写；
    对于区域的扫描通常都是双循环，有行\列两个参数，完美搭配了cells(x,y)，在help也是推荐cells写法的；

2、回到你的问题，对单元格，因为有行参数 i ，所以当前单元格的写法是：cells(i,7)，所有ells(f,7)肯定是不对的，但我认为
     Cells(i, "g") =  Cells(i, "d") / Cells(i, "C")
     应该是正确的。你这句之所以不能通过，实测是因为除数cells(i,"c")是零值，错误提示“溢出”，所以还要排除零值的情况：
     if cells(i,"c")<>0 then
        Cells(i, "g") =  Cells(i, "d") / Cells(i, "C")
     endif

ruhong18

hfwufanhf2006 发表于 2021-6-29 12:07
1、要先了解cells和range的写法不同：
     cells(x,y)：x就是行坐标，y是列坐标，跟数学坐标是一致的， ...

谢谢老师指点！
Sheets(2).Select
Dim a
For i = 2 To [a500].End(3).Row - 1
If Cells(i, "c") <> 0 Then
        Cells(i, "g") = Cells(i, "d") / Cells(i, "C")
           Cells(i, "g").NumberFormatLocal = "0.0"
      a = Cells(i, "g")
     End If

Next i



以上代码后，出现这种情况：遇上sheet1的数据行数减少时，sheet2中G列的数据会溢出，没有跟随最后一行的数据而终止；

hfwufanhf2006

ruhong18 发表于 2021-6-29 14:52
谢谢老师指点！
Sheets(2).Select
Dim a

要结合你的具体数据来分析，你之前的文件里sheet2这个表是空的，没有任何数据，所以实际上也看不出问题所在来；
有几个地方我要说一下：

1、Sheets(2).Select
     这行执行后，焦点会转移到sheet2表中，后续所有的操作都将限定在sheet2中，比如 [a65536].End(3).Row 会指向 sheet2 而不是之前的 sheet1，或许你的问题来自这里。

2、要先了解焦点的含义，尤其是录制宏得到的代码：
     a、焦点就是看得见的地方，任何时刻只能有一个sheet能被看见，你看见sheet1，就看不见sheet2的内容，在录制宏的时候随着手工切换焦点，都同时伴随着.select 就是这个原因。现在执行了 sheets(2).select，那么sheet1又看不见了，也就是说失去了焦点；
     b、在焦点内操作，代码是最简略的，比如要引用单元格 a5 ，range("a5")就行了；
          如果不在焦点内操作，就要引用全名，完整格式：
             sheets(1).range("a5")，多了一个表前缀，指定来自sheet1的 a5。
          我想你应该明白了，在执行sheets(2).select后，下面的区别：
             range("a5")：指sheet2中的 a5;
             worksheets(1).range("a5")：指第一个工作表的 a5，worksheets(1)与sheets(1)大部分情况下含义相同；

3、之前我说过，vba绝大多数都可以盲操作，只有少数几个涉及焦点的例外，所以原则上sheets(2).select 是不需要的；
     这并不是说 sheets(2).select 不能用，用了它你后面就要时刻关注引用的区别了，比如：
       For i = 2 To [a65536].End(3).Row - 1，[a65536].End(3).Row是指当前焦点表a列的末行，当前表就是焦点所在的表；
       If Cells(i, "c") <> 0 Then，同样，Cells(i, "c")也是指当前表；

4、跟外国人名类似，当不会引起歧义的时候用简称即可，当出现重复时就要用全名，下面是常用的全名格式：
     工作簿：workboos("文件名") ：通常只有一个工作簿打开，所以初学者这个不太会用到，如果是多文件操作，这个就必须会了；
     工作表：workboos("文件名").worksheets(“工作表名”)：在同一工作簿内操作，工作簿前缀可以省略，之前是写 sheets(2)而无工作簿前缀；
     单元格：worksheets(“工作表名”).range("a5")：多数是指当前工作表，所以工作簿前缀可以省略，但工作表前缀大多数是需要的；

     最后是代码所在表与当前工作表的关系：
        这两个一般来说就是一个，把代码放在哪个表，在执行代码的时候是需要切换到那个表你才能运行那段代码，这就决定了焦点会转移到代码所在的表，所以它们是一个。不过代码和源数据以及生成的结果都在同一个工作表内，所有的前缀都可以省略，之前我写代码就是这种最简单的情况。现在说到有sheet1和sheet2，你就需要仔细审查各种参数和单元格位置了；

如果还不行，你就需要上附件；