0

I write a code that is supposed to check what is going on cell and act on the cells next to it (add or delete value). However, after run code is crushed on this: Target.Offset(, 1).Value = "". Anyone has an idea what's wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
Dim lastRow As Long

lastRow = Cells(Rows.count, 5).End(xlUp).row

For i = 18 To lastRow
    If Range("E" & i).Value = "TEXT" Then
        Target.Offset(, 1).Value = ""
    End If
    
Next i

End Sub
vipmaciej
  • 13
  • 10
  • You're changing the worksheet again inside, causing the event to fire in an infinite loop. – BigBen Jul 09 '20 at 15:05
  • You need to disable events ```Application.EnableEvents = False ``` and turn them back on after. You're going into an infinite loop from changing the sheet in the worksheet change sub. – Warcupine Jul 09 '20 at 15:05
  • 1
    You need to run some checks on `Target` before doing anything. What if the user updated an entire row - how would you offset 1 column if the end of the range is already at the edge of the sheet? If you're only interested in changes in ColE then you can use `Application.Intersect(Target, Me.Columns("E"))` to check whether the update(s) were in that column. Plenty of examples of robust approaches here on SO. – Tim Williams Jul 09 '20 at 15:34
  • I add Application.EnableEvents = False as @Warcupine said, it work – vipmaciej Jul 10 '20 at 06:39

0 Answers0