CODEWITHSUNDEEP
excelvba
Matt Ridge
Matt Ridge

Reputation: 3651

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

I am getting a runtime error 1004 if I'm not on the same page that the script is meant to run on, and I'd like to know why...

here is the code.

Option Explicit

Sub PO_Tracking()

Dim wsPOD As Worksheet
Dim wsPOT As Worksheet
Dim wsPOA As Worksheet
Dim cel As Range
Dim lastrow As Long, i As Long, Er As Long

Set wsPOD = Sheets("PO Data")
Set wsPOT = Sheets("PO Tracking")
Set wsPOA = Sheets("PO Archive")

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

With wsPOD
    'first bring columns F:G up to match their line
    For Each cel In Intersect(.UsedRange, .UsedRange.Offset(5), .Columns(6))

        If cel = vbNullString And cel.Offset(, -2) <> vbNullString Then
            .Range(cel.Offset(1), cel.Offset(1, 1)).Copy cel
            cel.Offset(1).EntireRow.Delete
        End If

    Next

    'now fil columns A:D to match PO Date and PO#
    For Each cel In Intersect(.UsedRange, .UsedRange.Offset(5), .Columns(1))

        If cel = vbNullString And cel.Offset(, 5) <> vbNullString Then
            .Range(cel.Offset(-1), cel.Offset(-1, 3)).Copy cel
        End If
    Next

'Blow away rows that are useless
    lastrow = wsPOD.Range("A6").End(xlDown).Row
    wsPOD.Range("M5:P5").Copy wsPOD.Range("M6:P" & lastrow)
    Calculate

    With Intersect(wsPOD.UsedRange, ActiveSheet.Columns("N"))
        .AutoFilter 1, "<>Different"
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    With Intersect(wsPOD.UsedRange, ActiveSheet.Columns("P"))
        .AutoFilter 1, "<>Full"
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    wsPOD.UsedRange.Copy Sheets.Add.Range("A1")


'Final Adjustments before transfering over to new sheet.
    With ActiveSheet
        .AutoFilterMode = False
        Intersect(.UsedRange, .Columns("A")).Cut .Range("Q1")
        Intersect(.UsedRange, .Columns("D")).Cut .Range("R1")
        Intersect(.UsedRange, .Columns("C")).Cut .Range("S1")
        Intersect(.UsedRange, .Columns("B")).Cut .Range("T1")
        Intersect(.UsedRange, .Columns("G")).Cut .Range("U1")
        Intersect(.UsedRange, .Columns("F")).Cut .Range("V1")
        Intersect(.UsedRange, .Range("Q:V")).Copy wsPOT.Cells(Rows.Count, "B").End(xlUp).Offset(1)
        .Delete
    End With

    lastrow = wsPOD.Cells(Rows.Count, "B").End(xlUp).Row
    wsPOT.Range("R1:X1").Copy
    wsPOT.Range("B3:H" & lastrow).PasteSpecial xlPasteFormats
    wsPOT.Range("N2:O2").Copy wsPOT.Range("N3:O" & lastrow)
    wsPOT.Range("P1:Q1").Copy wsPOT.Range("I3:J" & lastrow)
    wsPOT.Range("K3:K" & lastrow).Borders.Weight = xlThin
End With



Application.CutCopyMode = False

End Sub

The error is here:

**With Intersect(wsPOD.UsedRange, ActiveSheet.Columns("N"))**
    .AutoFilter 1, "<>Different"
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Upvotes: 3

Views: 16233

Answers (3)

Andreas Covidiot
Andreas Covidiot

Reputation: 4745

  1. to avoid the error one has to check for equality of the worksheet (myRange.Parent) like this:

    if rng1.Parent is rng2.Parent then if Not Intersect( rng1, rng2 ) Is Nothing then _
    '... your conditional code here ...
    • hint: the important thing to notice here is that you can't connect the two conditions with ... And ... since VBA evaluates all conditions and does not stop after evaluating the first even if it is False :-/

  2. or make sure the range's worksheets are the same (e.g. ws1), meaning to explicitely specify/create/intersect your Range objects similar to this):

    if Not Intersect( ws1.Range("A1:A2"), ws1.Range("A2:B2") ) Is Nothing then _
   '... your conditional code here ...

Upvotes: 0

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

For why the error, see the answer by Doug Glancy.

In addition, for how to avoid it, use something like

Dim rng1 As Range, rng2 As Range
Set rng1 = wsPOD.UsedRange
Set rng2 = ActiveSheet.Columns("N")
If (rng1.Parent.Name = rng2.Parent.Name) Then
    Dim ints As Range
    Set ints = Intersect(rng1, rng2)
    If (Not (ints Is Nothing)) Then
        With ints
            ' Do your job
        End With
    End If
End If

It is typically good practice to verify an Intersection before using it.

Upvotes: 3

Doug Glancy
Doug Glancy

Reputation: 27478

You can't have an intersection of ranges on two sheets, so if ActiveSheet is not wsPOD, then

With Intersect(wsPOD.UsedRange, ActiveSheet.Columns("N"))

has to fail by definition.

EDIT ... and see @SiddharthRout's comment for the fix.

Upvotes: 8

Related Questions