Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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
excelmacros-vba
MS Office Version: 2016
posted by: aleutian (rep: 2) on Wed Jul 20, 2016 at 8:55 pm
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.

posted by: don (rep: 1989) on Wed Jul 20, 2016 at 9:35 pm
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