Excel VBA - Issue with Worksheet_Change Code

Question

I'm trying to create a macro that puts the date in a cell on another worksheet, when the initial worksheet is changed, but it gives me an out of range error. Is there any way to get around this, or am I simply unable to use the Worksheet_Change for this case. If so, what can I use? I was simply trying to test it, so I only have this so far:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    Worksheets("Sheet4").Activate
    Range("E1").End(xlDown).Offset(1, 0).Value = Date

    Application.EnableEvents = True

End Sub

I now have this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Worksheets("Testing Sheet").Range("E2").Value = "" Then
        Worksheets("Testing Sheet").Range("E2").Value = Date
    Else
       ' Worksheets("Testing Sheet").Range("E2").End(xlDown).Offset(1, 0).Value = Date
    End If

    Application.EnableEvents = True

End Sub

But the statement at the Else is giving me an error saying Application defined or object defined error. (side note I don't have it commented out in my actual code)

Check the following: 1) worksheet ("Sheet4") exists? Look for spelling / space on worksheet name 2) Where are you getting "Date" from? — Alex
– Alex, Commented Aug 5, 2014 at 14:45
@Alex I was able to get to the sheet. The sheet name was different but it was the 4th sheet, so I thought I had to call it "Sheet4" instead of its actual name, but the date prints in "Sheet5" still — Ganda
– Ganda, Commented Aug 5, 2014 at 14:50
Worksheets("Sheet4") explicitly looks for a worksheet named "Sheet4". If you intended to activate the 4th Sheet, go Sheets(4).Activate — Alex
– Alex, Commented Aug 5, 2014 at 14:53

Jagadish Dabbiru · Accepted Answer · 2014-08-05 15:54:05Z

1

You will get error because you are checking condition this

If Worksheets("Testing Sheet").Range("E2").Value = "" Then

For Suppose you have some value E2 Cell. So it goes to Else statement

Worksheets("Testing Sheet").Range("E2").End(xlDown).Offset(1, 0).Value = Date

But probably don't have data below E2 Cell. So .End(xlDown) Selects E1048576 Cell Which is the last allowed row supported by excel.

.Offset(1, 0).Value tries to point E1048577 Cell which is not supported.

So you get Application Defined Error. Hope this makes sense.

answered Aug 5, 2014 at 15:54

Jagadish Dabbiru

9519 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ganda Over a year ago

That makes so much sense. If I have a title in E1 that says "Date", can I just replace the E2 in that line of code with E1 and have it solve the problem? @Jagadish Dabbiru

Jagadish Dabbiru Over a year ago

You can actually use Range("E" & Rows.Count).End(xlUp).Row to find the last used row.

Collectives™ on Stack Overflow

Excel VBA - Issue with Worksheet_Change Code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related