0

I would like to capture the time of data entry (in column 1) for each value input to (column 5). I'm using this modified bit of code I got from here: Excel Formula which places date/time in cell when data is entered in another cell in the same row

This code functions as intended but still returns "Runtime Error '1004': Application Defined or Object Defined Error". Is there any way I can ignore this error, or perhaps fix the problem? I'm fairly new at this so bear with me.

  • My Target Column (5) Is where I'm checking for a number greater than 0, this is the trigger to update the time in Column 1

  • Column 5 has text cells above the range (E9:E50) I want to check. Which may be causing issue

  • When I go to debug, the second line of my code is highlighted with no other hints

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 5 And Target.Offset(0, -4).Value = "" Then
        If Target.Column > 0 Then
            Target.Offset(0, -4) = Format(Now(), "HH:MM:SS")
        End If
    End If
End Sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
Gabe Knowlton
  • 1
  • 1
    Even when the Column is not 5, the test of offset -4 column is performed, which may sometimes be less than 1. – Guest Apr 08 '19 at 17:37
  • 1
    The second part of the `And` is [going to execute](https://stackoverflow.com/q/345399/11683) even when the first part is false. You need to move the `Target.Offset(0, -4).Value = ""` condition into a nested `If`. Otherwise you will be getting errors when the column number is less than 5. You do not need the `Target.Column > 0` condition, it is always true. Move the `Target.Offset(0, -4).Value = ""` in its place. – GSerg Apr 08 '19 at 17:38
  • So fast, thank you! This was just what I needed. – Gabe Knowlton Apr 08 '19 at 17:52
  • I see your edits, I had the check for "Target.Column > 0", but that was not the right code for what I wanted, I should have had "Target.Value > 0", because I have the values default to 0 in those cells. Thanks again for the detailed response. – Gabe Knowlton Apr 08 '19 at 19:24

0 Answers0