none
Runtime Error '1004':, Method 'Intersect' of object '_Global' failed RRS feed

  • Question

  • Hello

    I am getting a runtime error 1004, can someone tell me why?  I am getting the runtime error on the first Application.Intercept statement.

    Thank you for your help!

    smsemail

    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        If Not Application.Intersect(Target, Me.Range("A:A")) Is Nothing Then
           lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
           If lastRow < 17 Then
              Exit Sub
           End If
           If lastRow > 67 Then
              lastRow = 67
           End If
        Else
           Exit Sub
        End If
        
        
        If Not Application.Intersect(Target, Me.Range("A17:A" & lastRow)) Is Nothing Then
           Application.EnableEvents = False
           
           If Application.WorksheetFunction.CountA(Worksheets("RIPS").Range("A17:A67")) = 0 Then
              Exit Sub
           End If
           
           If Application.WorksheetFunction.CountA(Worksheets("RIPS").Range("B17:B67")) = 0 And _
              Application.WorksheetFunction.CountA(Worksheets("RIPS").Range("C17:C67")) = 0 And _
              Application.WorksheetFunction.CountA(Worksheets("RIPS").Range("D17:D67")) = 0 Then
              Exit Sub
           End If
           
           If CmdExecute = True Then
              Exit Sub
           End If
           
           If CmdClear = True Then
              Exit Sub
           End If
           
          
           Worksheets("RIPSSummary").Activate
           lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
           Set SourceRange = Application.Intersect(Range("A2:A" & lastRow), ActiveSheet.UsedRange)
           MsgBox "Source Range: " & SourceRange
           
           Worksheets("RIPS").Activate
           lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
           Set TargetRange = Application.Intersect(Range("A17:A" & lastRow), ActiveSheet.UsedRange)
           MsgBox "Target Range: " & TargetRange
           
           Exit Sub
           wsDeleted = False
           For Each acell In SourceRange.Cells
               RecordFound = True
               If Not IsEmpty(acell.Value) Then
                  Set C = TargetRange.Find(acell.Value, LookIn:=x1values)
           
                  If C Is Nothing Then
                     RecordFound = False
                  End If
                  If RecordFound = False Then
                     wsDeleted = True
                     For Each Worksheet In Worksheets
                        If Worksheet.Name = acell.Value Then
                           Worksheet.Delete
                        End If
                     Next Worksheet
                  End If
               End If
           Next acell
        
           If vbKeyDelete Or _
              vbKeyClear Then
              r = lastRow
              Do Until r < 17
                 If Worksheets("RIPS").Range("A" & r).Value = "" Then
                    Rows(r).Delete
                 End If
                 r = r - 1
              Loop
           End If
           
           Application.EnableEvents = True
        End If
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
           
    End Sub

    Tuesday, January 6, 2015 5:36 PM

All replies

  • It should work but maybe there's something about your workbook we can't see.

    In passing generally best not to disable screenupdating, alerts or events unless need to do so. More importantly though you should ensure they always get reset. As written you have several Exit Sub's before any code that resets them. Also in case of an error consider resetting them in an error handler.

    Tuesday, January 6, 2015 7:45 PM
    Moderator
  • Hello

    I do not fully understand the "Intersect" method.  Can you explain?

    thanks

    smsemail

    Tuesday, January 6, 2015 7:53 PM
  • Intersect returns a Range object that refers to any cells in the given ranges that overlap all the given ranges, if any. If there are no cells overlap all the given ranges Intersect will return Nothing. 

    See Help and the example.

    Tuesday, January 6, 2015 10:27 PM
    Moderator
  • Hi smsemail,

    Base on your code, I can’t reproduce that issue, you may share a simple project that can reproduce that issue on the OneDrive, we will check it.

    Regards


    • Edited by Clearly09 Thursday, January 8, 2015 1:28 AM
    Wednesday, January 7, 2015 8:32 AM