嵌套循环优化需求：Excel宏多日期工时统计代码重构

我明白你现在的困扰——原来的代码靠硬编码重复逻辑实现多日期统计，不仅冗余难维护，还卡在了跨工作表循环引用的问题上。下面是优化后的代码，用嵌套循环替代冗余代码，同时解决跨表引用的问题，还能提升运行效率：

Public Function SubAssyArray() As Variant
    ' 定义需遍历的工作表数组
    SubAssyArray = Array("Widget1", "Widget2")
End Function

Sub Resource_Overview()
    ' 按工种汇总多日期任务
    Application.ScreenUpdating = False
    Application.StatusBar = "Macro is running..."
    Application.Calculation = xlCalculationManual
    
    ' 声明变量
    Dim ws As Worksheet
    Dim targetWs As Worksheet ' Sheet1：存储要统计的7个日期
    Dim rnWs As Worksheet ' ResourceNeeds：输出统计结果
    Dim lastRow As Long, lastColumn As Long
    Dim i As Long, j As Long, dateCol As Long
    Dim assyTotal As Double, solderTotal As Double, qcTotal As Double, ppcTotal As Double
    Dim targetDate As Variant
    Dim dateRange As Range ' Sheet1中B1:H1的日期区域
    
    ' 初始化工作表对象（直接引用，避免Activate）
    Set targetWs = ThisWorkbook.Worksheets("Sheet1")
    Set rnWs = ThisWorkbook.Worksheets("ResourceNeeds")
    Set dateRange = targetWs.Range("B1:H1") ' 要遍历的7个日期
    
    ' --- 第一步：统一设置日期格式（只执行一次）---
    For Each ws In ThisWorkbook.Worksheets(SubAssyArray())
        With ws
            lastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            lastColumn = .Cells(12, .Columns.Count).End(xlToLeft).Column - 3
            .Range(.Range("I12"), .Cells(lastRow, lastColumn)).Style = "Comma"
        End With
    Next ws
    
    ' --- 第二步：遍历每个目标日期，统计工时 ---
    dateCol = 2 ' 结果输出从ResourceNeeds的B列开始
    For Each targetDate In dateRange
        ' 重置工时计数器
        assyTotal = 0
        solderTotal = 0
        qcTotal = 0
        ppcTotal = 0
        
        ' 遍历每个Widget工作表统计
        For Each ws In ThisWorkbook.Worksheets(SubAssyArray())
            With ws
                lastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
                lastColumn = .Cells(12, .Columns.Count).End(xlToLeft).Column - 3
                
                ' 遍历列（日期列）和行（任务行）
                For i = 9 To lastColumn
                    For j = 12 To lastRow
                        ' 匹配目标日期
                        If .Cells(j, i).Value = targetDate.Value Then
                            ' 按工种累加工时
                            Select Case .Cells(1, i).Value
                                Case "Assy": assyTotal = assyTotal + .Cells(7, i).Value
                                Case "Solder": solderTotal = solderTotal + .Cells(7, i).Value
                                Case "QC": qcTotal = qcTotal + .Cells(7, i).Value
                                Case "PPC": ppcTotal = ppcTotal + .Cells(7, i).Value
                            End Select
                        End If
                    Next j
                Next i
            End With
        Next ws
        
        ' 将统计结果写入ResourceNeeds对应列
        rnWs.Cells(2, dateCol) = ppcTotal
        rnWs.Cells(3, dateCol) = assyTotal
        rnWs.Cells(4, dateCol) = solderTotal
        rnWs.Cells(5, dateCol) = qcTotal
        
        dateCol = dateCol + 1 ' 移动到下一列输出
    Next targetDate
    
    ' --- 第三步：恢复日期格式（只执行一次）---
    For Each ws In ThisWorkbook.Worksheets(SubAssyArray())
        With ws
            lastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            lastColumn = .Cells(12, .Columns.Count).End(xlToLeft).Column - 3
            .Range(.Range("I12"), .Cells(lastRow, lastColumn)).NumberFormat = "mm/dd/yy;@"
        End With
    Next ws
    
    ' 恢复系统设置
    rnWs.Select
    Application.StatusBar = "Macro is complete..."
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    MsgBox "The Macro has finished running."
    
    ' 释放对象变量
    Set ws = Nothing
    Set targetWs = Nothing
    Set rnWs = Nothing
    Set dateRange = Nothing
End Sub

关键改进说明： #

• 取消不必要的工作表激活：直接通过ThisWorkbook.Worksheets()引用工作表对象，避免反复切换工作表，既提升运行速度又减少屏幕闪烁。
• 外层循环遍历多日期：通过For Each targetDate In dateRange循环Sheet1的B1:H1区域，自动获取每个要统计的日期，无需硬编码。
• 复用统计逻辑：把单日期的统计代码嵌套在日期循环内，每次循环重置工时变量，完成统计后直接写入ResourceNeeds的对应列，彻底消除冗余代码。
• 格式设置只执行一次：把日期格式的修改和恢复移到日期循环外面，只执行两次（修改+恢复），避免重复操作浪费资源。
• 明确的对象命名：给工作表变量起清晰的名字（如targetWs、rnWs），避免混淆跨工作表引用的对象。

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