时间戳

首页 > 解决方案 > 需要自动用户名和时间的帮助

excel - 需要自动用户名和时间的帮助

问题描述

我正在使用 Excel，我需要在其中获取两个条目的示例 1 的自动用户名和时间。如果我在第 2 列中插入一个值，我需要在单元格“A”和“O”2 处输入用户名和时间。在同一个 Excel 中再次为 Column(7) 中的条目工作表我需要在单元格“I”和“N”中的日期和用户名

我的代码适用于单点，我很困惑如何使用两次。

检查我尝试并建议的代码

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, c As Range
    Set rng = Application.Intersect(Target, Me.Columns(2))
    If rng Is Nothing Then Exit Sub
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            If Len(c.Offset(0, -1).Value) = 0 Then
                With c.EntireRow
                    .Cells(1, "A").Value = Now()
                    '.Cells(1, "B").Value = Date
                    .Cells(1, "O").Value = Environ("username")
                End With
            End If
        End If

    Set rng = Application.Intersect(Target, Me.Columns(7))
    If rng Is Nothing Then Exit Sub
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            If Len(c.Offset(0, -1).Value) = 0 Then
                With c.EntireRow
                    .Cells(1, "I").Value = Now()
                    '.Cells(1, "B").Value = Date
                    .Cells(1, "N").Value = Environ("username")
                End With
            End If
        End If
    Next c
End Sub

标签： excelvba

解决方案

有问题的行是If rng Is Nothing Then Exit Sub因为如果Target不在第 2 列中，它会退出 sub 并且永远不会到达第 7 列的第二个测试。

所以If Not rng Is Nothing Then改用：

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, c As Range
    Set rng = Application.Intersect(Target, Me.Columns(2))
    If Not rng Is Nothing Then
        For Each c In rng.Cells
            If Len(c.Value) > 0 Then
                If Len(c.Offset(0, -1).Value) = 0 Then
                    With c.EntireRow
                        .Cells(1, "A").Value = Now()
                        '.Cells(1, "B").Value = Date
                        .Cells(1, "O").Value = Environ("username")
                    End With
                End If
            End If
        Next c
    End If

    Set rng = Application.Intersect(Target, Me.Columns(7))
    If Not rng Is Nothing Then
        For Each c In rng.Cells
            If Len(c.Value) > 0 Then
                If Len(c.Offset(0, -1).Value) = 0 Then
                    With c.EntireRow
                        .Cells(1, "I").Value = Now()
                        '.Cells(1, "B").Value = Date
                        .Cells(1, "N").Value = Environ("username")
                    End With
                End If
            End If
        Next c
    End If
End Sub

推荐阅读