Excel VBA 遍历结构化表指定列后无法获取其他列对应值的问题

你代码的核心问题是所有形如Range("thisIsMyTable[列名]").Cells的写法，指代的是该列的全部单元格集合，而非当前遍历行的对应单元格。即使添加.Value也只会拿到包含整列所有值的数组，无法直接赋值给单个日期变量，因此后续逻辑全部失效。

推荐两种可行写法：

写法1：基于原有循环添加行索引 #

仅需在原有代码基础上增加当前行的索引定位，修改后代码如下：

For Each valCell In Range("thisIsMyTable[Task Status]").Cells
    If valCell = "Finished" Then
        ' 计算当前行在结构化表列中的位置索引
        Dim currentRow As Long
        currentRow = valCell.Row - Range("thisIsMyTable[Task Status]").Row + 1
        
        ' 按索引取对应行的列值
        dateScheduled = Range("thisIsMyTable[End Date]").Cells(currentRow).Value
        dateRelease = Range("thisIsMyTable[Release Date]").Cells(currentRow).Value
        dateReady = Range("thisIsMyTable[Date Ready]").Cells(currentRow).Value
        totalFinishCat = totalFinishCat + 1
        daysToFinished = daysToFinished + DateDiff("d", dateReady, dateRelease)
        
        If Range("thisIsMyTable[Time Spent]").Cells(currentRow).Value = "" Then
            timeTotalFinished = timeTotalFinished + Range("thisIsMyTable[Time estimate]").Cells(currentRow).Value + Range("thisIsMyTable[Extra hours]").Cells(currentRow).Value
        Else
            timeTotalFinished = timeTotalFinished + Range("thisIsMyTable[Time Spent]").Cells(currentRow).Value
        End If
        
        If dateRelease >= dateStartReport Then
            monthFinished = monthFinished + 1
            timeMonthFinished = timeMonthFinished + Range("thisIsMyTable[Time Spent]").Cells(currentRow).Value
            daysToFinishedMonth = daysToFinishedMonth + DateDiff("d", dateReady, dateRelease)
            If dateRelease > dateScheduled Then
                afterDue = afterDue + 1
                diff = DateDiff("d", dateScheduled, dateRelease)
                afterDay = afterDay + diff
            Else
                beforeDue = beforeDue + 1
                diff = DateDiff("d", dateRelease, dateScheduled)
                beforeDay = beforeDay + diff
            End If
        End If
    End If
Next valCell

写法2：使用ListObject规范操作结构化表 #

直接操作VBA内置的结构化表对象ListObject，不需要计算行偏移，可读性和稳定性更高：

Dim tbl As ListObject
Dim tblRow As ListRow
' 绑定当前工作表的结构化表
Set tbl = ActiveSheet.ListObjects("thisIsMyTable")

For Each tblRow In tbl.ListRows
    ' 判断当前行的任务状态
    If tblRow.Range(tbl.ListColumns("Task Status").Index).Value = "Finished" Then
        ' 按列名读取当前行的对应值
        dateScheduled = tblRow.Range(tbl.ListColumns("End Date").Index).Value
        dateRelease = tblRow.Range(tbl.ListColumns("Release Date").Index).Value
        dateReady = tblRow.Range(tbl.ListColumns("Date Ready").Index).Value
        
        totalFinishCat = totalFinishCat + 1
        daysToFinished = daysToFinished + DateDiff("d", dateReady, dateRelease)
        
        If tblRow.Range(tbl.ListColumns("Time Spent").Index).Value = "" Then
            timeTotalFinished = timeTotalFinished + tblRow.Range(tbl.ListColumns("Time estimate").Index).Value + tblRow.Range(tbl.ListColumns("Extra hours").Index).Value
        Else
            timeTotalFinished = timeTotalFinished + tblRow.Range(tbl.ListColumns("Time Spent").Index).Value
        End If
        
        If dateRelease >= dateStartReport Then
            monthFinished = monthFinished + 1
            timeMonthFinished = timeMonthFinished + tblRow.Range(tbl.ListColumns("Time Spent").Index).Value
            daysToFinishedMonth = daysToFinishedMonth + DateDiff("d", dateReady, dateRelease)
            If dateRelease > dateScheduled Then
                afterDue = afterDue + 1
                diff = DateDiff("d", dateScheduled, dateRelease)
                afterDay = afterDay + diff
            Else
                beforeDue = beforeDue + 1
                diff = DateDiff("d", dateRelease, dateScheduled)
                beforeDay = beforeDay + diff
            End If
        End If
    End If
Next tblRow

建议提前声明所有变量类型，比如Dim dateScheduled As Date、Dim dateRelease As Date，避免变量类型不匹配导致的计算错误。

内容的提问来源于stack exchange，提问作者crissc