如何使用Visual Basic在Excel行更新时条件添加或更新日期

需求梳理 #

• 新增行时自动在P列对应单元格填入当日创建日期
• A到S列任意单元格更新时，U列自动填入该行最后更新日期，格式为mm/dd/yyyy
• D列值变更时根据状态自动填充对应日期：
  • D列变为Approved时，R列填入当日日期
  • D列变为Funded时，S列填入当日日期
  • 后续同类状态需求可直接在代码中扩展

原有代码问题说明 #

你提交的代码存在三处明显缺陷：

1. 缺少新增行时P列自动填充创建日期的逻辑
2. 使用ActiveCell.Column判断D列变更不可靠，批量修改D列内容时规则会失效
3. 仅在单元格不为空时更新最后编辑日期，清空单元格的操作不会被记录

修正后完整代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range, roww As Long
    Dim rng As Range
    ' 定义可触发规则的操作范围
    Set WorkRng = Intersect(Range("A:S"), Target)
    If Not WorkRng Is Nothing Then
        ' 关闭事件防止修改单元格时触发死循环
        Application.EnableEvents = False
        For Each rng In WorkRng
            roww = rng.Row
            ' 1. 新增行判断：如果P列当前为空，说明是第一次编辑该行，填入创建日期
            If Cells(roww, "P").Value = "" Then
                Cells(roww, "P").Value = Date
                Cells(roww, "P").NumberFormat = "mm/dd/yyyy"
            End If
            ' 2. 任意修改都更新最后编辑日期（U列）
            Cells(roww, "U").Value = Now
            Cells(roww, "U").NumberFormat = "mm/dd/yyyy"
            ' 3. D列变更时根据状态填对应日期
            If rng.Column = 4 Then ' 4对应D列，比判断ActiveCell更可靠
                Select Case rng.Value
                    Case "Approved"
                        Cells(roww, "R").Value = Date
                        Cells(roww, "R").NumberFormat = "mm/dd/yyyy"
                    Case "Funded"
                        Cells(roww, "S").Value = Date
                        Cells(roww, "S").NumberFormat = "mm/dd/yyyy"
                    ' 后续新增状态直接在这里加Case即可
                End Select
            End If
        Next
        ' 恢复事件触发
        Application.EnableEvents = True
    End If
End Sub

使用说明 #

1. 打开你要设置功能的Excel工作表
2. 按Alt+F11打开VBA编辑器
3. 在左侧工程窗口双击对应工作表，把上面的代码粘贴到右侧代码窗口
4. 保存文件为Excel 启用宏的工作簿(*.xlsm)格式即可生效

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