Excel宏功能扩展：多列数据迁移及复选框日期计算需求

需求说明 #

• 现有功能：通过复选框选择，迁移C列、D至J列的数据；当每行O列值为1时，同步迁移K列数据
• 新增功能：根据复选框对应的星期偏移值（周一=0，周二=1，…，周日=6），将Begin!F9的基准日期加上对应偏移天数后，填充到OtherSheet!Q2单元格（日期格式）

修改后的宏代码 #

Sub Demo()
Dim lastRow As Long, arrData, i As Long, arrRes()
Dim Row_Cnt As Long, iR As Long, j As Long, iC As Long
Const COL_BASE = 4
Dim aWeek, aCheck(), Chk_Cnt As Long
Dim baseDate As Date, targetDate As Date, offsetDays As Integer
Dim isChecked As Boolean

aWeek = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
ReDim aCheck(UBound(aWeek))
isChecked = False
offsetDays = 0

' 获取复选框状态并计算日期偏移量
For i = 0 To UBound(aWeek)
    ' Forms控件状态判断
    aCheck(i) = (ActiveSheet.Shapes("chk" & aWeek(i)).OLEFormat.Object.Value = 1)
    If aCheck(i) Then
        Chk_Cnt = Chk_Cnt + 1
        ' 记录选中星期对应的偏移天数（周一0，周二1...周日6）
        offsetDays = i
        isChecked = True
        ' 如需仅取第一个选中的复选框，取消下面一行注释
        ' Exit For
    End If
Next

' 日期填充逻辑
If isChecked Then
    ' 读取基准日期
    baseDate = ThisWorkbook.Worksheets("Begin").Range("F9").Value
    ' 计算目标日期
    targetDate = DateAdd("d", offsetDays, baseDate)
    ' 写入目标单元格并设置日期格式
    With ThisWorkbook.Worksheets("OtherSheet").Range("Q2")
        .Value = targetDate
        .NumberFormat = "yyyy/mm/dd" ' 可按需调整日期显示格式
    End With
End If

lastRow = Cells(Rows.Count, "C").End(xlUp).Row
If lastRow > 8 And Chk_Cnt > 0 Then
    arrData = Range("A9:O" & lastRow)
    Row_Cnt = UBound(arrData)
    ReDim arrRes(1 To Row_Cnt, 1 To Chk_Cnt + 2)
    iR = 0
    For i = LBound(arrData) To Row_Cnt
        If arrData(i, 15) = 1 Then
            iR = iR + 1
            arrRes(iR, 1) = arrData(i, 3)
            iC = 2
            For j = 0 To UBound(aCheck)
                If aCheck(j) Then
                    arrRes(iR, iC) = arrData(i, COL_BASE + j)
                    iC = iC + 1
                End If
            Next
            arrRes(iR, iC) = arrData(i, 11)
        End If
    Next
    ' 输出结果从A20开始，可按需修改
    Range("A20").Resize(iR, iC).Value = arrRes
End If
End Sub

关键修改说明 #

1. 日期偏移计算：遍历复选框时，直接用数组索引对应星期偏移值（周一对应索引0，偏移0天，以此类推）
2. 多选处理：默认记录最后一个选中的复选框偏移值；如需仅取第一个选中项，取消代码中Exit For的注释即可
3. 异常防护：通过isChecked变量判断，避免无复选框选中时执行日期相关代码引发错误
4. 格式设置：给目标单元格明确设置日期格式，确保显示符合要求

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