Runtime error in VBA - Automatically Input Date/Time When Cells are Filled

0

I am trying to get an excel spreadsheet to input the date and time automatically whenever I start to fill in a column. It works just fine if I set the ofset for the output to a positive number but I need the date and time to show in the first column. So I set the offset to a negative number to put it in the column BEFORE the input data and gives me a runtime error, but go back to the worksheet and it inputs the date and time before the input column like it is supposed to. SO for example I want to enter info into column B and it shows the date and time in column A, gives an error. If i set it to enter info into column B and show the date and time in column C it works with no issues. Thanks in advance!

Example of code that is giving me issues:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Offset(0, -1).Value = "" Then
        Target.Offset(0, -1) = Format(Now())
    End If
End Sub

code that works but wrong column:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Offset(0, 1).Value = "" Then
        Target.Offset(0, 1) = Format(Now())
    End If
End Sub
Answer
Discuss

Answers

1
Selected Answer

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        If Cells(Target.Row, (Target.Column - 1)).Value = "" Then
            Cells(Target.Row, Target.Column - 1) = Format(Now())
        End If
    End If
End Sub

I think the issue was that it was sometimes trying to offset off of the worksheet and this prevents that by putting everything inside the first IF statement.

I removed the Offset functionality just because I prefer this setup, but that shouldn't matter as long as everything is inside the first IF statement anyway.

Discuss

Discussion

Fantastic, works perfect, thank you very much!
aleutian (rep: 2) Jul 20, '16 at 9:50 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login