运行时错误'1004':,对象'_Global'的方法'Intersect'失败 [英] Runtime Error '1004':, Method 'Intersect' of object '_Global' failed
本文介绍了运行时错误'1004':,对象'_Global'的方法'Intersect'失败的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果我不在脚本要运行的页面上,则会收到运行时错误1004,我想知道为什么...
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...
这是代码.
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
错误在这里:
**With Intersect(wsPOD.UsedRange, ActiveSheet.Columns("N"))**
.AutoFilter 1, "<>Different"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
推荐答案
在两个图纸上不能有范围的交集,因此,如果ActiveSheet不是wsPOD,则
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"))
必须按照定义失败.
编辑...,然后查看@SiddharthRout的评论以获取修复.
EDIT ... and see @SiddharthRout's comment for the fix.
这篇关于运行时错误'1004':,对象'_Global'的方法'Intersect'失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文