Excel VBA调试：循环不搜索整个范围

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:

我写了一个基本宏来搜索范围(在一张纸上),然后根据保存选择值的第三张纸将所选单元格复制到另一张纸张。我已经使用了循环i = x到y bu它看起来像宏正在跳过一些行!???!即,如果要在行1到4上复制4个有效值,则宏仅复制第2行和第4行的值(丢弃有效单元1和3)。这是代码:

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

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

为什么代码会从第2行和第4行中选择和复制值(它的接缝就像使用+1步骤一样?)谢谢。

2 个解决方案

#1

You are defining RowCount as CountA + 1:

您将RowCount定义为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 think you are therefore overwriting your results? Removing one of the +1's should do the trick.

我认为你因此覆盖你的结果?删除+ 1中的一个应该可以解决问题。

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!

编辑:很高兴看到有人定义他们复制的范围,而不是使用一堆Select的...绝对更好的编码风格!

#2

I think you're adding a +1 to the RowCount in two places.

我想你在两个地方为RowCount添加了+1。

...
RowCount = Application.WorksheetFunction.CountA(PasteSheet.Range("A:A")) + 1
...
Set CopyValueID_Paste = PasteSheet.Range("A" & RowCount + 1) '<-- Remove the +1 here

A few other notes - nice code overall --------------------------

一些其他说明 - 总体上很好的代码--------------------------

As pointed out in the comments, there is a pretty agreed upon best practice to find the RowCount: RowCount = Range("A" & .Rows.Count).End(xlUp).Row + 1. This will solve the problem of possible blank cells in the column, which would throw off the COUNTA.

正如评论中所指出的,有一个非常商定的最佳实践来找到RowCount:RowCount = Range(“A”和.Rows.Count).End(xlUp).Row + 1.这将解决可能的问题列中的空白单元格,将甩掉COUNTA。
You can do this without COPY and PASTE with CopyValueID_PASTE.value = CopyValueID.Value. It will make a huge efficiency difference if you are doing this over a lot of rows.

您可以使用CopyValueID_PASTE.value = CopyValueID.Value进行COPY和PASTE。如果您在很多行中执行此操作,它将产生巨大的效率差异。

#1