vba VBA将数据添加到Excel 2010中的最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15288026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA adding data to last row in Excel 2010
提问by SamL
I'm newbie to VBA. I'm trying to find some data in Sheet1 and copy it to Sheet2. When I select the data from Sheet1, I need to append it to that already in Sheet2.
我是 VBA 的新手。我试图在 Sheet1 中找到一些数据并将其复制到 Sheet2。当我从 Sheet1 中选择数据时，我需要将它附加到已经在 Sheet2 中的数据中。
I can find and paste the data using the following code, but I can't append it. What have I done wrong in my code?
我可以使用以下代码查找并粘贴数据，但无法附加它。我在代码中做错了什么？
Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim i As Long
Dim NewSh As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
MyArr = Array("37", "283", "300", "112", "1100", "336", "98")
Set NewSh = Sheets("Sheet2")
With Sheets("Sheet1").Range("B5:B500")
Rcount = 0
For i = LBound(MyArr) To UBound(MyArr)
Set Rng = .Find(What:=MyArr(i), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rcount = Rcount + 1
Rng.EntireRow.Copy NewSh.Range("A" & Rcount)
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next i
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
回答by Kazimierz Jawor
I guess that 'append' means to add data when you run the macro again and again. It this situation next time you run will overwrite what you have in sheet2. You need to change Rcount variable into:
我猜“追加”意味着在您一次又一次地运行宏时添加数据。下次运行时这种情况将覆盖您在 sheet2 中的内容。您需要将 Rcount 变量更改为：
Rcount = NewSh.Cells(NewSh.Rows.Count,1).End(xlUp).Row+1
to find first empty cell in Sheet2 in column A.
Some additional changes will be required if you run the code for the first time (if you really need to have the results in row 1). Check also where to place Rcountincrement line- rather should be moved after .copyline
在 A 列的 Sheet2 中找到第一个空单元格。如果您第一次运行代码（如果您确实需要在第 1 行中获得结果），则需要进行一些额外的更改。还要检查在哪里放置Rcount增量线 - 而应该.copy在行之后移动
