Hello,
I the below sub in order to enter timestamps into a table; the last column of the table works out the duration between each timestamp but, for some reason, this script stops the formula in the last column from copying down as new rows are added to the table. It's the most bizare thing. That column isn't even unlocked for editing and the ranges don't crossover into that column at all. Has anyone experienced this before? Any ideas how to fix?
The target range is the "NAME" column and my script posts the timestamp into the "TIME ON" column next to the name entered and also the "TIME OFF" column in the row above with the "DUR." column being the duration. I've tested this out and the formula remains fine with no VBA on the sheet but stops working I enter this sub:
-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'The ranges that we are checking is where the names are
If Not Intersect(Target, Range("B10:B100")) Is Nothing Then
On Error Resume Next
'If the value is empty
If Target.Value = "" Then
'Make sure that the relevant cells are empty
Target.Offset(0, 3) = ""
Target.Offset(-1, 4) = ""
Else
'Otherwise put the timestamp in the "TIME ON" & "TIME OFF" cols
Target.Offset(0, 3).Value = Format(Now, "mm/dd/yy HH:mm")
Target.Offset(-1, 4).Value = Format(Now, "mm/dd/yy HH:mm")
End If
End If
End Sub
----------------------------------------------------------------------------------
Cell "B10" is where the "NAME" column starts.
I the below sub in order to enter timestamps into a table; the last column of the table works out the duration between each timestamp but, for some reason, this script stops the formula in the last column from copying down as new rows are added to the table. It's the most bizare thing. That column isn't even unlocked for editing and the ranges don't crossover into that column at all. Has anyone experienced this before? Any ideas how to fix?
The target range is the "NAME" column and my script posts the timestamp into the "TIME ON" column next to the name entered and also the "TIME OFF" column in the row above with the "DUR." column being the duration. I've tested this out and the formula remains fine with no VBA on the sheet but stops working I enter this sub:
-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'The ranges that we are checking is where the names are
If Not Intersect(Target, Range("B10:B100")) Is Nothing Then
On Error Resume Next
'If the value is empty
If Target.Value = "" Then
'Make sure that the relevant cells are empty
Target.Offset(0, 3) = ""
Target.Offset(-1, 4) = ""
Else
'Otherwise put the timestamp in the "TIME ON" & "TIME OFF" cols
Target.Offset(0, 3).Value = Format(Now, "mm/dd/yy HH:mm")
Target.Offset(-1, 4).Value = Format(Now, "mm/dd/yy HH:mm")
End If
End If
End Sub
----------------------------------------------------------------------------------
Cell "B10" is where the "NAME" column starts.