Run-time error '1004': Method 'Intersect' of object' _Global' failed

Question

I'm still fairly new at this and was trying to find an answer. Maybe it's not defined correctly or at all. Maybe it's not pointing to correct work sheet. I'm not really sure... Any help would be greatly appreciated! Thanks!

Getting error on this line:

Set Inte = Intersect(A, Target)

Error code is:

Run-time error '1004': Method 'Intersect' of object'_Global' failed

Full code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine Target Colunm
 If Target.Column = 10 Then
'Check for "TD", Copy/Delete Row if found
  If Target = "TD" Then
    Application.EnableEvents = False
      nxtRow = Sheets("TD Locks").Range("J" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Copy _
        Destination:=Sheets("TD Locks").Range("A" & nxtRow)
      Target.EntireRow.Delete
      Application.EnableEvents = True
       Exit Sub
  End If

'Check for "Closed", Copy/Delete Row if found
  If Target = "Closed" Then
    Application.EnableEvents = False
      nxtRow = Sheets("Closed Locks").Range("J" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Copy _
        Destination:=Sheets("Closed Locks").Range("A" & nxtRow)
      Target.EntireRow.Delete
      Application.EnableEvents = True
  End If
 End If

'Adds date when borrower name is entered
    Dim A As Range, B As Range, Inte As Range, r As Range
    Set A = Range("C:C")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In Inte
            If r.Offset(0, 8).Value = "" Then
               r.Offset(0, 8).Value = Date
            End If
        Next r
    Application.EnableEvents = True
End Sub

user3598756 user3598756 · Accepted Answer · 2016-04-06T14:57:04

there's a "devil touch" in your code since if the user types "Closed" in column "J" of the sheet in whose module you're placing this event handler, it deletes the target row (Target.EntireRow.Delete), thus leaving target unreferenced and preparing the ground for throwing an error in any subsequent use of target, which happens to be in Set Inte = Intersect(A, Target)

But If I correctly read your code, this shouldn't even happen since this latter line gets done only should target cross column "C", which can't be if it's in column "J"!.

If what above is correct you may want to use a code like the following

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow As Long
Dim Inte As Range, r As Range

Application.EnableEvents = False

With Target
    'Determine Target Colunm
    If .Column = 10 Then

        'Check for "Closed", Copy/Delete Row if found
        If .Value = "Closed" Then
            nxtRow = Sheets("Closed Locks").Range("J" & Rows.Count).End(xlUp).Row + 1
            .EntireRow.Copy _
            Destination:=Sheets("Closed Locks").Range("A" & nxtRow)
            .EntireRow.Delete

        ElseIf Target = "TD" Then
            'Check for "TD", Copy/Delete Row if found
            nxtRow = Sheets("TD Locks").Range("J" & Rows.Count).End(xlUp).Row + 1
            .EntireRow.Copy _
            Destination:=Sheets("TD Locks").Range("A" & nxtRow)
            .EntireRow.Delete
        End If

    Else

        'Adds date when borrower name is entered
        Set Inte = Intersect(.Cells, .Parent.Range("C:C"))
        If Not Inte Is Nothing Then
            For Each r In Inte
                If r.Offset(0, 8).Value = "" Then r.Offset(0, 8).Value = Date
            Next r
        End If

    End If
End With

Application.EnableEvents = True

End Sub

Run-time error '1004': Method 'Intersect' of object' _Global' failed

2 Answers