For Each Next 循环意外地跳过了一些条目 [英] For Each Next loop unexpectedly skipping some entries
问题描述
我一直在 Excel 中编写一个宏,它扫描记录列表,查找内容中带有CHOFF"的任何单元格,复制包含它的行,并将这些单元格粘贴到另一个工作表中.它是格式化报告的较长代码的一部分.
I have been coding a macro in Excel that scans through a list of records, finds any cells with "CHOFF" in the contents, copying the row that contains it, and pasting those cells into another sheet. It is part of a longer code that formats a report.
它工作得很好,除了For Each"循环似乎随机跳过了一些条目.它不是每隔一行,我已经尝试对其进行不同的排序,但是无论如何都会跳过相同的单元格,因此它似乎与单元格的顺序无关.我尝试使用 InStr 而不是 cell.value,但仍然跳过相同的单元格.
It has worked just fine, except that the "For Each" loop has been skipping over some of the entries seemingly at random. It isn't every other row, and I have tried sorting it differently, but the same cells are skipped regardless, so it doesn't seem to be about order of cells. I tried using InStr instead of cell.value, but the same cells were still skipped over.
您知道是什么原因导致代码无法识别分散在范围内的某些单元格吗?
Do you have any idea what could be causing the code just not to recognize some cells scattered within the range?
有问题的代码如下:
Dim Rng As Range
Dim Cell As Range
Dim x As Integer
Dim y As Integer
ActiveWorkbook.Sheets(1).Select
Set Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
x = 2
For Each Cell In Rng
If Cell.Value = "CHOFF" Then
Cell.EntireRow.Select
Selection.Cut
ActiveWorkbook.Sheets(2).Select
Rows(x).Select
ActiveWorkbook.ActiveSheet.Paste
ActiveWorkbook.Sheets(1).Select
Selection.Delete Shift:=xlUp
y = x
x = y + 1
End If
Next Cell
推荐答案
For Each...Next
循环不会自动跟踪您删除了哪些行.当您删除一行时,Cell
仍然指向相同的地址(现在是原始地址下方的行,因为它已被删除).然后在下一轮循环中,Cell
移动到下一个单元格,跳过一个.
The For Each...Next
loop doesn't automatically keep track of which rows you have deleted. When you delete a row, Cell
still points to the same address (which is now the row below the original one, since that was deleted). Then on the next time round the loop, Cell
moves onto the next cell, skipping one.
要解决这个问题,您可以在 If
语句中将 Cell
上移一个(例如使用 Set Cell = Cell.Offset(-1,0)代码>).但我认为这是简单的
For
循环优于 For Each
的罕见情况之一:
To fix this, you could move Cell
up one within the If
statement (e.g. with Set Cell = Cell.Offset(-1,0)
). But I think this is one of the rare cases where a simple For
loop is better than For Each
:
Dim lngLastRow As Long
Dim lngSourceRow As Long
Dim lngDestRow As Long
Dim objSourceWS As Worksheet
Dim objDestWS As Worksheet
Set objSourceWS = ActiveWorkbook.Sheets(1)
Set objDestWS = ActiveWorkbook.Sheets(2)
lngLastRow = objSourceWS.Range("C" & objSourceWS.Rows.Count).End(xlUp).Row
lngDestRow = 1
For lngSourceRow = lngLastRow To 1 Step -1
If objSourceWS.Cells(lngSourceRow, 3).Value = "CHOFF" Then
objSourceWS.Rows(lngSourceRow).Copy Destination:=objDestWS.Cells(lngDestRow, 1)
objSourceWS.Rows(lngSourceRow).Delete
lngDestRow = lngDestRow + 1
End If
Next lngSourceRow
这会向后循环(根据 Portland Runner 的建议)以避免对已删除的行执行任何操作.它还整理了代码中的其他一些内容:
This loops backwards (as per Portland Runner's suggestion) to avoid having to do anything about deleted rows. It also tidies up a couple of other things in your code:
- 你不需要做任何
Select
ing,而且最好不要(见这个问题 为什么) - 您可以在
Range.Copy
中指定目的地,而不必进行单独的选择和粘贴 - 您可以就地"更改变量的值,而无需先将其分配给第二个变量(即
x = x + 1
很好) - 对于包含行号的变量,您应该使用
Long
而不是Integer
,因为 Excel 电子表格中的行多于Integer
可以处理(至少 65536,而Integer
的最大值为 32767)
- You don't need to do any
Select
ing, and it's better not to (see this question for why) - You can specify a destination within
Range.Copy
rather than having to do a separate select and paste - You can change the value of a variable "in place" without having to assign it to a second variable first (i.e.
x = x + 1
is fine) - you should use
Long
rather thanInteger
for variables that contain row numbers, since there are more rows in an Excel spreadsheet than anInteger
can handle (at least 65536 compared to 32767 max for anInteger
)
显然测试它仍然可以满足您的要求!
Obviously test that it still does what you require!
这篇关于For Each Next 循环意外地跳过了一些条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!