运行时错误'1004',设置变量时应用程序定义的或对象定义的错误= cell.offset
这是我的代码。它贯穿始于Cell L1
的值列表,在另一个工作表中找到它们,然后找到偏移单元格。如果该单元格包含"TEMPLATE"
它将从原始列表中删除该值。运行时错误'1004',设置变量时应用程序定义的或对象定义的错误= cell.offset
Sub Removetemplate()
Dim x As Variant
Dim myString As String
Dim temprng As Range
Dim cell As Object
' Set numrows = number of rows of data.
Worksheets("WorkingSO").Activate
NumRows = Range("L2", Range("L2").End(xlDown)).Rows.Count
' Select cell L2.
Set SORNG = Range("L2")
SORNG.Select
' Establish "For" loop to loop "numrows" number of times.
Worksheets("Sales Orders").Activate
For x = 1 To NumRows
For Each cell In Sheet1.Cells
If cell.Value = SORNG.Value Then
Set temprng = cell.Offset(28, -17)
myString = temprng.Value
If InStr(myString, "TEMPLATE") > 0 Then
SORNG.ClearContents
End If
End If
Next
' Selects cell down 1 row from active cell.
Set SORNG = SORNG.Offset(1, 0)
Next
End Sub
1004 error
发生在行Set temprng = cell.Offset(28, -17)
。林不是100%肯定,但我相信它是第一次通过,但第二次循环失败。
它因为For Each cell In Sheet1.Cells
循环通过的Sheet1
所有细胞,因此它也通过细胞循环属于第1列至17这导致cell.Offset(28, -17)
失败,因为它会尝试达成“负列”小区
此外,你必须避免Activate
/Select
编码习惯和使用完全合格范围参考
试试这个代码:
Option Explicit
Sub Removetemplate()
Dim SOCell As Range, WSOCell As Range
Dim WSORng As Range
' Set numrows = number of rows of data.
With Worksheets("WorkingSO")
Set WSORng = .Range("L2", .Range("L2").End(xlDown)) 'set "WorkingSO" worksheet range to loop through
End With
With Worksheets("Sales Orders") '<--| reference "Sales Orders" worksheet
For Each WSOCell In WSORng '<--| loop through WSORng cells
For Each SOCell In .Columns("R").SpecialCells(xlCellTypeConstants) '<--| loop through currently referenced worksheet (i.e. "Sales Orders") column "R" cells with any constant value
If SOCell.Value = WSOCell.Value Then '<--| if current "Sales Orders" cell value matches current "WorkingSO" one
If InStr(SOCell.Offset(28, -17), "TEMPLATE") > 0 Then WSOCell.ClearContents '<--| if "Sales Orders" cell offsetted 28 rows down and 17 columns left form current one has "TEMPLATE", then clear current "WorkingSO" cell
End If
Next
Next
End With
End Sub
其中您必须将.Columns("R")
更改为实际的“销售订单”工作表列索引,您要在
谢谢。这个伎俩。我对此非常陌生,现在只是试图调整避免主动/选择的最干净的做法。我仍然不明白,如果每个“cell.Value = SORNG.Value”实例出现在列Y后面,它将如何得到“cell.Offset(28,-17)”,值为17或更小 –
欢迎您。因为我没有你的数据,所以不会说为什么它会出现在这样的列索引中。但是你可以遍历你之前的代码(F8键)并查询直接窗口(?cell.Value,cell.Address,cell.Parent.Name)来查看实际发生的事情。 – user3598756
中查找“WorkingSO”列“L”值,没有小于17的行的实例,其中单元格.value = SORNG.value –
@ConnorHoward在'Set tmprng'行放置一个断点,点击断点并键入'?cell.Address'。你遇到了一个错误,那里*是行号问题。 –
没有。并且偏移量中的第二个值将调整列而不是行。但最近的专栏是Z(26) –