当某列的同一行中的单元格发生更改时，如何在该列中输入日期/时间戳

Question

我有一张Excel工作表，其中的列A:N可以用文本填充。

当这些单元格中的任何一个都填充了文本时，我希望用日期/时间戳更新(同一行的(单元格O。

我所看到的解决方案使用"；偏移"；函数，但我不能使用它。

Private Sub Worksheet_Change(ByVal Target As Range) 
If Intersect(Target, Range("A1:O1000")) Is Nothing Then Exit Sub 
Target.Offset(x,y).Value = Date 
End Sub

Answer 1

为更改的单元格行添加时间戳

片材模块(例如Sheet1(

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Const sCols As String = "A:N" ' Source Columns
Const dCol As String = "O" ' Destination Column
Const FirstRow As Long = 2

Dim rCount As Long: rCount = Rows.Count - FirstRow + 1
Dim rOffset As Long: rOffset = FirstRow - 1
Dim sRng As Range ' Source Range
Set sRng = Intersect(Columns(sCols).Resize(rCount).Offset(rOffset), Target)

If Not sRng Is Nothing Then

Dim TimeStamp As Date: TimeStamp = Now
Dim dcRng As Range ' Destination Column Range
Set dcRng = Columns(dCol).Resize(rCount).Offset(rOffset)

Dim dRng As Range ' Destination Range
Dim aRng As Range ' Area Range

For Each aRng In sRng.Areas
If dRng Is Nothing Then
Set dRng = Intersect(aRng.EntireRow, dcRng)
Else
Set dRng = Union(dRng, Intersect(aRng.EntireRow, dcRng))
End If
Next aRng

Application.EnableEvents = False
dRng.Value = TimeStamp
Application.EnableEvents = True

End If

End Sub
Sub testNonContiguous()
' No time stamp in cell 'O1' if 'FirstRow > 1'.
Range("A1,C4,F11").Value = 1
End Sub