时间戳

首页 > 解决方案 > 尝试遍历数组范围内的单元格时，VBA 获得“需要对象”

arrays - 尝试遍历数组范围内的单元格时，VBA 获得“需要对象”

问题描述

我正在尝试创建一个“For Each”循环，其中代码将获取存储在数组中的范围并循环遍历它们，如果单元格为空，则将单元格的颜色更改为橙​​色。我可以输入数组中的每个单元格，但为了节省空间和时间，我已经说明了范围。我可以通过单个 for 循环运行数组，但随后他会将整个范围视为单个值，因此我也尝试遍历范围，遍历每个单元格。我收到“需要对象”错误，我很难理解我在哪里犯了错误。

我试图确定变量的不同类型的数据类型，因为我相信这是错误所在，这没有结果。

Private Sub CommandButton2_Click()

Dim CellCheckList As Variant
Dim ChRng As Ranges
Dim ChCell As Range


CellCheckList = Array("E1", "I1", "B3:B8", "B9:F9", "B14:F14", "J14:J19", "B25", "C26", "B27:B28", _
                "C29", "B30:F31", "C32:C33", "B34:F35", "C36", "B37:B39", "B42:F42", "C43:C44", _
                "B45", "B48:B56", "C54:F54", "B77", "B78:B84", "C82:F82", "B88:F88", "C89", _
                "C92:C94", "B95:B97", "C97:F97", "C98")

    For Each ChRng In CellCheckList
        For Each ChCell In ChRng
            If IsEmpty(Sheets("time sheet").Range(ChCell)) = True Then
                Sheets("time sheet").Range(ChCell).Interior.Color = 49407 
            Else
                Sheets("time sheet").Range(ChCell).Interior.Color = 16772300
            End If
        Next
    Next

标签： arraysexcelvbafor-loop

解决方案

尝试：

Option Explicit

Private Sub CommandButton2_Click()

    Dim rngList As Range, cell As Range

    With ThisWorkbook.Worksheets("time sheet")

        Set rngList = .Range("E1, I1, B3:B8, B9:F9, B14:F14, J14:J19, B25, C26," & _
                                "B27:B28,C29, B30:F31, C32:C33, B34:F35, C36, B37:B39," & _
                                "B42:F42, C43:C44, B45, B48:B56, C54:F54, B77, B78:B84," & _
                                "C82:F82, B88:F88, C89,C92:C94, B95:B97, C97:F97, C98")

        For Each cell In rngList

            If IsEmpty(cell) = True Then
                cell.Interior.Color = 49407
            Else
                cell.Interior.Color = 16772300
            End If

        Next cell

    End With

End Sub

推荐阅读