对于每个下一个循环意外地跳过一些条目 [英] For Each Next loop unexpectedly skipping some entries

问题描述

我已经在Excel中编写了一个宏，用于扫描记录列表，在内容中找到具有CHOFF的任何单元格，复制包含它的行，并将这些单元格粘贴到另一个工作表中。它是格式化报告的较长代码的一部分。

它的工作很好，除了For Each循环已经随机地跳过了一些条目。它不是每一行，我已经尝试排序不同，但相同的单元格被跳过，无论如何，似乎没有关于单元格的顺序。我尝试使用InStr而不是cell.value，但仍然跳过相同的单元格。

你有什么想法可能导致代码不能识别分散在范围内的细胞？

有关代码如下：

  Dim Rng As Range 
 Dim Cell As Range 
 Dim x As Integer 
 Dim y As Integer 
 
 ActiveWorkbook.Sheets（1）.Select 
 
设置Rng =范围（范围（C1），范围（ C& Rows.Count）.End（xlUp））
x = 2 
 
每个单元格在R $ 
 
如果Cell.Value =CHOFF然后
 Cell.EntireRow.Select 
 Selection.Cut 
 ActiveWorkbook.Sheets（2）。选择
行（x）。选择
 ActiveWorkbook.ActiveSheet.Paste 
 ActiveWorkbook.Sheets（1）。选择
 Selection.Delete Shift：= xlUp 
y = x 
x = y + 1 
如果
 
下一个单元格
  

解决方案

For Each .. .Next 循环不会自动跟踪哪个ro你已经删除了当您删除一行时，单元格仍然指向相同的地址（现在是原始地址下的行，因为已被删除）。然后在下一次循环时， Cell 移动到下一个单元格，跳过一个。

要解决这个问题你可以在中移动 Cell 如果语句（例如使用 Set Cell = Cell .Offset（-1,0））。但是，我认为这是罕见的情况之一，一个简单的对于循环比对于每个：

  Dim lngLastRow As Long 
 Dim lngSourceRow As Long 
 Dim lngDestRow As Long 
 Dim objSourceWS As Worksheet 
 Dim objDestWS As Worksheet 
 
设置objSourceWS = ActiveWorkbook.Sheets（1）
设置objDestWS = ActiveWorkbook.Sheets（2）
 
 lngLastRow = objSourceWS.Range （C& objSourceWS.Rows.Count）.End（xlUp）.Row 
 
 lngDestRow = 1 
对于lngSourceRow = lngLastRow到1步-1 
如果objSourceWS。单元格（lngSourceRow，3）.Value =CHOFF然后
 objSourceWS.Rows（lngSourceRow）.Copy目标：= objDestWS.Cells（lngDestRow，1）
 objSourceWS.Rows（lngSourceRow）.Delete 
 lngDestRow = lngDestRow + 1 
结束If 
下一页lngSourceRow 
  

向后（根据波特兰运动员的建议），以避免不必要关于删除的行。它还在你的代码中整理了一些其他的东西：

• 你不需要做任何选择 ing，最好不要（请参阅这个问题为什么）


• 您可以在 Range.Copy 中指定目的地，而不必单独选择和粘贴


• 您可以将变量的值原位置更改，而无需先将其分配给第二个变量（即 x = x + 1你应该使用 Long 而不是整数用于包含行号的变量，因为Excel电子表格中的行数多于 Integer 可以处理的行数（至少为65536，而$ code>整数）

显然测试它仍然是你所需要的！

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.

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?

The code in question is below:

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

解决方案

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.

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

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:

• You don't need to do any Selecting, 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 than Integer for variables that contain row numbers, since there are more rows in an Excel spreadsheet than an Integer can handle (at least 65536 compared to 32767 max for an Integer)

Obviously test that it still does what you require!

这篇关于对于每个下一个循环意外地跳过一些条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！