时间戳

首页 > 解决方案 > For循环问题中的VBA Excel IsError

excel - For循环问题中的VBA Excel IsError

问题描述

我收到的每日报告在几个工作表中包含一堆分组表格。我感兴趣的每个表中只有某些行。每行中的单元格值中都有一个子集，用于确定它是否是我感兴趣的东西。该值需要与我在另一张表中的列中的值匹配。一旦找到匹配项，我就可以确定要应用到该行的颜色。

我已经在这方面花费了很多时间，到目前为止，我尝试过的所有可以运行的东西都得到了一些我想要工作的东西，但不是全部。一旦我让它为第一个表工作，我应该能够缩放宏来处理工作簿的其余部分。

我首先将 MyList 定义为要与另一张表进行比较的单元格范围。然后我设置了其他变量，当我扩大规模时我最终会改变这些变量。然后我从整个工作簿中删除现有的条件格式，因为一旦我完成了这一切，我就不需要它了。然后我激活我想要处理的工作表。for 循环似乎运行良好。我为 NB 变量设置了一个手表，我可以看到每个循环的更新正确。我遇到的问题是，无论我对 IsError(Match()) 做什么，它似乎都不会随着 for 循环的每次迭代而更新。所以它要么在整个时间内保持为假并为表格中的每一行着色，要么在整个时间内保持为真并且不着色（取决于我尝试的代码排列方式）。

我做了一堆谷歌搜索和阅读，但找不到正确的信息来弄清楚如何解决这个问题。我是在尝试以我不应该的方式使用某些东西，还是我没有正确定义某些东西？我还缺少什么？

我是 VBA 的新手，多年来并没有真正完成任何编码工作。所以，除了解决这个问题，我真的很感兴趣它背后的原因，所以我希望不会重复这些错误。

Set MyList = Worksheets("Sheet1").Range("A1", "A500")
Dim NBcol As Integer, MZcol As Integer, blcol As Integer, NB As String
NBcol = 13
MZcol = 16
blcol = 12

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Cells.FormatConditions.Delete
Next ws

Worksheets("Sheet2").Activate
For i = 7 To 26
    NB = Left(Cells(i, NBcol), 6)
    If Not IsError(Application.Match(NB, MyList, 0)) Then
        If Cells(i, MZcol) >= 3.5 Then
            ActiveSheet.Range(Cells(i, blcol), Cells(i, MZcol)).Interior.Color = RGB(250, 191, 143)
        Else
            ActiveSheet.Range(Cells(i, blcol), Cells(i, MZcol)).Interior.Color = RGB(197, 217, 241)
        End If
    Else
    End If
Next i

标签： excelvba

解决方案

这是一个显然不是您的解决方案的示例，而是可以回答您的一些问题的良好实践示例。您必须修改它以适应您的情况。

  1. 尽量避免“幻数”。使用描述数字存在原因的名称创建一个常量。
  2. 尝试并保持您的顶级逻辑“可读”。如果您可以创建只出现一次或重复出现的独立代码块，它可以更清楚地显示您的逻辑“流程”。
  3. 建立使用与您正在使用的数据或操作类型密切相关的名称的“工作”变量。在本例中，我展示了三个工作表对象。您应该选择对您和您的应用程序有意义的名称。
  4. 始终完全限定您的工作表引用。

这是代码：

Option Explicit

Private Const NBcol As Long = 13
Private Const MZcol As Long = 16
Private Const BLcol As Long = 12

Sub CompareMyDataExample()

    ClearAllConditionalFormats

    Dim listWS As Worksheet
    Dim dataWS As Worksheet
    Dim infoWS As Worksheet
    Set listWS = ThisWorkbook.Sheet1
    Set dataWS = ThisWorkbook.Sheets("My Data")
    Set infoWS = ThisWorkbook.Sheet2

    Const START_ROW As Long = 7
    Const END_ROW As Long = 26

    Dim myList As Range
    Dim lastRow As Long
    With listWS
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set myList = .Range("A1").Resize(lastRow, 1)
    End With

    Dim i As Long
    For i = START_ROW To END_ROW
        Dim nb As Long
        nb = infoWS.Cells(i, NBcol).Value
        If Not IsError(Application.Match(nb, myList, 0)) Then
            If infoWS.Cells(i, MZcol).Value >= 3.5 Then
                '--- always include ALL worksheet references with every
                '    reference to a part of a worksheet
                infoWS.Range(infoWS.Cells(i, BLcol), infoWS.Cells(i, MZcol)).Interior.Color = RGB(250, 191, 143)
            Else
                '--- as an alternative, you can set up a "with" block
                '    but ALWAYS remember to use the "." to make sure it
                '    links back to the "with" block reference
                With infoWS
                    .Range(.Cells(i, BLcol), .Cells(i, MZcol)).Interior.Color = RGB(197, 217, 241)
                End With
            End If
        End If
    Next i

End Sub

Private Sub ClearAllConditionalFormats()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.FormatConditions.Delete
    Next ws
End Sub

推荐阅读