Excel VBA调试：循环不搜索整个范围 [英] Excel VBA Debug: Loop not searching through the whole range

问题描述

我写了一个基本的宏来搜索一个范围（在一个表格中），然后根据包含选择值的第三页复制所选单元格（到另一个单元格）。我已经使用循环，因为i = x到y bu它看起来像宏跳过一些行！
即如果在第1行到第4行有4个有效值被复制，则宏仅复制第2行和第4行的值（将有效单元格1和3分开）。这是代码：

I wrote a basic macro to search through a range (in one sheet) and then copy the selected cell (to another sheet) based on a 3rd sheet holding the selection values. I've used the loop as for i = x to y bu it looks like the macro is skipping some rows !???! i.e. If there are 4 valid values to be copied on rows 1 to 4, the macro only copies values from rows 2 and 4 (ditching the valid cells 1 and 3). Here's the code:

Sub XXXXX()
Dim i As Integer
Dim PasteSheet As Worksheet: Set PasteSheet = Sheets("Sheet1")
Dim CopySheet As Worksheet: Set CopySheet = Sheets("Sheet2")
Dim SearchSheet As Worksheet: Set SearchSheet = Sheets("Sheet3")
Dim LookupID, LookupID_SearchRange, CopyValueID, CopyValueID_Paste As Range

For i = 7 To 2000 'I've also used the (Step 1) option with no success
RowCount = Application.WorksheetFunction.CountA(PasteSheet.Range("A:A")) + 1 'finds the last cell to be used for copy

Set LookupID = CopySheet.Range("A" & i) 'searches all values within column A from row 7 to 2000
Set LookupID_SearchRange = SearchSheet.Range("A:A") ' Seaches if the values from Sheet3 are present in Sheet 1
Set CopyValueID = CopySheet.Range("X" & i) 'counter that follows the same search on A but selects values on X
Set CopyValueID_Paste = PasteSheet.Range("A" & RowCount) 'When it finds the ID, it copies some cell to the last row in Sheet2
      ' Initially  there was an additional RowCount (+1) for CopyValueID. Corrected based on answers for future refrence of the cleaned code.
If Not IsError(Application.Match(LookupID, LookupID_SearchRange, 0)) Then
    If CopyValueID.Value <> "" Then
        CopyValueID.Copy
        CopyValueID_Paste.PasteSpecial xlPasteValues
    End If
End If
Next i

End Sub

为什么代码选择和复制第2行和第4行的值（它的接缝就像使用+1步？）
谢谢。

Why does the code selects and copies values from rows 2 and 4 (it seams like it's using a +1 step?) Thanks.

推荐答案

您正在将 RowCount code> CountA + 1 ：

You are defining RowCount as CountA + 1:

RowCount = Application.WorksheetFunction.CountA(PasteSheet.Range("A:A")) + 1 'finds the last cell to be used for copy

那么你实际上在你粘贴的行中再添加一个：

then you actually add one again in the row you are pasting:

Set CopyValueID_Paste = PasteSheet.Range("A" & RowCount + 1) ' When it finds the ID, it copies some cell to the last row in Sheet2

I认为你是覆盖你的结果？删除其中一个 +1 应该做的伎俩。

I think you are therefore overwriting your results? Removing one of the +1's should do the trick.

编辑：很高兴看到有人定义范围他们复制到和从，而不是使用一堆选择的...绝对更好的编码风格！

Editorial: Happy to see someone defining the ranges they copy to and from, rather than using a bunch of Select's... definitely better coding style!

这篇关于Excel VBA调试：循环不搜索整个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！