0

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)

Improve this question
3
  • Check the following: 1) worksheet ("Sheet4") exists? Look for spelling / space on worksheet name 2) Where are you getting "Date" from? 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 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 Commented Aug 5, 2014 at 14:53

1 Answer 1

Reset to default
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.

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

2 Comments

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
You can actually use Range("E" & Rows.Count).End(xlUp).Row to find the last used row.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.