时间戳

首页 > 解决方案 > 如果我清除单元格 B，则 VBA 清除单元格 C

excel - 如果我清除单元格 B，则 VBA 清除单元格 C

问题描述

我是 VBA 的新手，最近几天一直在努力让它工作。我有 2 列。

B-学生 C-日期

我想要的是当一个学生进来并将他们的姓名首字母放在 B 列中，然后在该行的 C 列中填写日期。

现在，如果我删除学生的姓名缩写，我希望它也清除该行的 C 单元格。

这是我的代码

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wb As Workbook
Set wb = Workbooks("Training")
Dim ws As Worksheet
Set ws = wb.Worksheets("Sheet1")
Dim StaffRange As Range
Set StaffRange = ws.Range("B5:B40")
Dim StaffTime As Range

' If they put in initials in StaffRange then proceed
If Not Intersect(Target, StaffRange) Is Nothing Then
Set StaffTime = ws.Range("C" & Target.Row)
If StaffTime.Value <> "" Then Exit Sub 'if there is already a date then exit
StaffTime.Value = Now     ' put in the date time
    
'now if they clear StaffRange then clear StaffTime
ElseIf Intersect(Target, StaffRange) Is Nothing Then
Set StaffTime = ws.Range("C" & Target.Row)
StaffTime.ClearContents     ' make blank

End If

End Sub

感谢您的任何帮助。

标签： excelvba

解决方案

你可以这样做：

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim b As Range, c As Range, rng As Range
    'updates in range of interest?
    Set rng = Application.Intersect(Me.Range("B5:B40"), Target)
    If rng Is Nothing Then Exit Sub 'nothing to process...
    
    For Each b In rng.Cells
        Set c = b.Offset(0, 1)
        If Len(b.Value) > 0 Then
            If Len(c.Value) = 0 Then c.Value = Now 'value entered: add time
        Else
            c.ClearContents                        'value cleared: clear time
        End If
    Next b
    
End Sub

推荐阅读