0

I'm getting the run-time error "Method '_Default' of object 'Range' failed" when using the following macro in my excel document.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 2 Then Exit Sub
    Cells(Target.Row, "H") = Date
End Sub

It only happens sometimes, but when it does I cannot debug as it freezes and closes excel before reopening.

I am trying to update the date in column H when a row is edited. I found this code online and modified it to fit my situation.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
RABronn
  • 1
  • 1
    You're modifying the sheet, causing the event to fire in an endless loop. Disable events: `On Error GoTo SafeExit`, `Application.EnableEvents = False`, `Cells(Target.Row, "H") = Date`, `SafeExit:`, `Application.EnableEvents = True`. – BigBen May 25 '22 at 14:27
  • Or add `If Target.Column = 8 Then Exit Sub` – Tim Williams May 25 '22 at 15:45
  • That makes sense and I can see that now, thank you both. I went with Tim Williams solution since it's simpler, though I will try the other solution if I continue getting an error. – RABronn May 25 '22 at 15:57

0 Answers0