英文:
VBA to delete cell data if cell data in another cell is deleted
问题
我需要在单元格中输入数据时创建一个时间戳，以跟踪何时添加信息。
我找到了下面的代码，它基本上完美地工作。
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K4:K148")) Is Nothing Then
Target.Offset(0, 1) = Now
End If
End Sub
现在我的问题是，当我删除列K中的数据以开始新的一天的文件时，即使删除数据，列L中的时间戳也会更新。是否有更新的代码可以在删除列K中的相应单元格时删除列L中的时间戳。例如，我在K4中输入数据，就会在L4中获得K4输入的时间戳。如果我删除K4中的数据，我希望L4也被删除。
以下是显示数据输入到列K（1、2、3）和列L中显示时间戳的图片。第二张图片显示3已被删除，时间戳已更新。
英文:
I needed to create a timestamp if data was entered into a cell to track when information is being added.
I found the following code that works perfectly, mostly.
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K4:K148")) Is Nothing Then
Target.Offset(0, 1) = Now
End If
End Sub
My problem now is that when I delete the data in column K to start a fresh file for the next day, the timestamp in column L updates even when I delete the data. Is there an update to the code that can delete the timestamp in column L if the corresponding cell in column K is deleted. Example, I enter data in to K4, I get a timestamp in L4 of when K4 was entered. If I delete the data in K4, I want L4 to delete.
Pictures here showing data entered into column K (1,2,3) and column L showing the timestamps. Second picture shows 3 being deleted with the timestamp updating.
答案1
得分: 2
使用IsEmpty。正如评论中提到的Len(cell.Value)=0也可以工作。
Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Me.Range("K4:K148"))
If Not rng Is Nothing Then
On Error GoTo SafeExit
Application.EnableEvents = False
Dim cell As Range
For Each cell In rng
cell.Offset(, 1).Value = IIf(IsEmpty(cell.Value), "", Now)
Next
End If
SafeExit:
Application.EnableEvents = True
End Sub
英文:
Using IsEmpty. Len(cell.Value)=0 as mentioned in comments would also work.
Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Me.Range("K4:K148"))
If Not rng Is Nothing Then
On Error GoTo SafeExit
Application.EnableEvents = False
Dim cell As Range
For Each cell in rng
cell.Offset(,1).Value = IIf(IsEmpty(cell.Value), "", Now)
Next
End If
SafeExit:
Application.EnableEvents = True
End Sub
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论