Excel VBA复制粘贴(不使用Copy+粘贴函数)到空行

I've done this before and I'm scratching my head to why I can't figure it out.

我以前也这么做过，我都搞不懂为什么我搞不懂。

I'm trying to pull data from one sheet and paste it into another sheet on the next row. I've done this before without using "copy" or "paste" VBA functions.. I just used values and I would like to make it easier on the user that way (no random copied things on the excel clipboard)

我试图从一个表中提取数据并将其粘贴到下一行的另一个表中。我以前没有使用“复制”或“粘贴”VBA函数。我只是使用了值，我想让用户更方便(在excel剪贴板上没有随机复制的东西)

Here is my code:

这是我的代码:

Sub SubmitChange()
row_number = 2

Do
DoEvents
Row_number = row_number + 1
item_in_review = Sheet44.Range("B" & row_number)
Loop Until item_in_review = ""

Sheet44.Range("B" & row_number) = Sheet43.Range(A6).Value

End Sub

1 个解决方案

#1

Try this:

试试这个:

Option Explicit

Sub SubmitChange()

    Dim lastRow As Long

    lastRow = Sheet44.Cells(Sheet44.Rows.Count, 2).End(xlUp).Row + 1

    Sheet44.Range("B" & lastRow).Value2 = Sheet43.Range("A6").Value2

End Sub

。

Notes:

注:

You should always use Option Explicit at the top of the module

您应该始终在模块顶部使用显式选项
- it will force you to properly declare variables
- 它将迫使您正确地声明变量
If you copied and pasted your code without any changes, there is a subtle issue:

如果您在没有任何更改的情况下复制和粘贴您的代码，有一个微妙的问题:
- your variable "Row_number" is defined outside the scope of this sub (global)
- 变量“Row_number”定义在子(全局)的范围之外
- inside this sub you have a second variable named "row_number" (lower case "r")
- 在这个子变量中有第二个变量名为"row_number"(小写的"r")
- the loop increments the global variable "Row_number"
- 循环增加全局变量“Row_number”
- but in the last line the value gets always assigned to Sheet44.Range("B2")
- 但在最后一行中，值总是被分配给Sheet44.Range(“B2”)
- "row_number" (local to this sub) never gets incremented as you'd expect
- “row_number”(这个子节点的本地)永远不会像您预期的那样增加

The other issue is this Sheet43.Range(A6).Value should be this Sheet43.Range("A6").Value

另一个问题是这个Sheet43.Range(A6)。值应该是这个Sheet43.Range(“A6”)。

#1