VBA Deleting Rows for Changed Cells Debug Error

user_13344757 0
Viewed 29

The following does what I want it to by adding formulas when a value is entered into the Target cell, and then deletes said value when the cell is empty.

However, I keep running into a Debug Error if I were to right-click and delete that row within the Target Range, is there a way to prevent this from happening?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C11:C1000")) Is Nothing Then
      If Target.Value <> "" Then
        Target.Offset(0, -1).Formula = "=VLOOKUP(" & Target.Address & ",UIDs!$F$3:$H$750,3,FALSE)"
      Else:
        Target.Offset(0, -1).Value = ""
      End If
    End If
End Sub

Debug Error:

enter image description here

Then it takes me to If Target.Value <> "" Then if I click Debug.

vba excel
1 Answers

You can confirm that Target is only 1 cell (as it will be a lot more than that when you delete a row):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C11:C1000")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        If Target.Value <> "" Then
            Target.Offset(0, -1).Formula = "=VLOOKUP(" & Target.Address & ",UIDs!$F$3:$H$750,3,FALSE)"
        Else
            Target.Offset(0, -1).Value = ""
        End If
        Application.EnableEvents = True
    End If
End Sub
user_188247270
Related