vba 运行时错误“1004”：对象“_Global”的方法“Intersect”失败

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

提问by Matt Ridge

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...

如果我不在脚本要运行的同一页面上，我会收到运行时错误 1004，我想知道为什么...

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

回答by Doug Glancy

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

你不能在两张纸上有范围的交集，所以如果 ActiveSheet 不是 wsPOD，那么

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

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

has to fail by definition.

根据定义必须失败。

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

编辑...并查看@SiddharthRout 对修复的评论。

回答by sancho.s ReinstateMonicaCellio

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

有关错误的原因，请参阅 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.

Intersect在使用离子之前对其进行验证通常是一种很好的做法。

回答by Andreas Dietrich

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 Rangeobjects similar to this):

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

1. 为了避免错误，必须像这样检查工作表( myRange.Parent) 的相等性：

   if rng1.Parent is rng2.Parent then if Not Intersect( rng1, rng2 ) Is Nothing then _
       '... your conditional code here ...
   

   • 提示：这里要注意的重要一点是你不能连接这两个条件，... And ...因为 VBA 评估所有条件并且在评估第一个条件后不会停止，即使它是False：-/

2. 或确保范围的工作表相同（例如ws1），这意味着明确指定/创建/与您的Range对象相交与此类似）：

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