vba 从一列复制数据，粘贴到第一个空列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10453211/
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
Copy data from one column, paste in first empty column
提问by CJG
I have data in COLUMN H2. . I want to copy them somewhere else on the same spreadsheet. But where I copy it will change one COLUMN at a time, as each column below gets filled, by the macro.
我在 COLUMN H2 中有数据。. 我想将它们复制到同一个电子表格的其他地方。但是我复制它的地方会一次改变一个 COLUMN，因为下面的每一列都被宏填充了。
My existing macro successfully searches for, and pastes this column of data into an empty column below, such as B31. When I have 10 new pieces of data, the next time I run the macro, I want it to paste it into C31, in Column C (being the next empty row) The next time, D31.......etc.. My macro repeatedly grabs the data in H2:H11 and pastes it into B31, but overwrites that column each time I run the macro again. It is not "seeing" column B already having data placed there by running the macro before.... What is wrong with the code?
我现有的宏成功搜索并将此列数据粘贴到下面的空列中，例如 B31。当我有 10 条新数据时，下次我运行宏时，我希望将其粘贴到 C31 中，在 C 列（即下一个空行）下一次，D31.......等。 . 我的宏反复抓取 H2:H11 中的数据并将其粘贴到 B31 中，但每次我再次运行宏时都会覆盖该列。它不是“看到”列 B 之前通过运行宏已经将数据放置在那里......代码有什么问题？
Range("H2:H11").Select Selection.Copy
Range("A31").Select
Range("A31:M31").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Rows("1:10").EntireRow.Select
Application.CutCopyMode = False
回答by Sam
Range("A31:M31").End(xlToLeft).Offset(0, 1).Select
should be
应该
Range("M31").End(xlToLeft).Offset(0, 1).Select
Alternative solution is below (i've not tested it, as i'm not on my MS PC, sorry)
替代解决方案如下（我没有测试过，因为我不在我的 MS PC 上，抱歉）
dim lRow as Long 'Output Row
lRow = 31 'Start at row 31
For i = 2 to 13 'Column B to Column M
'Count if there is any values in the columns
'(if there is a header then change the 0 to 1)
If Application.WorksheetFunction.CountA(ActiveSheet.Columns(i)) = 0 Then
For Each c in Range("H2:H11") 'Cycle through output values
ActiveSheet.Cells(lRow, i).value = c.Value 'Assign values
lRow = lRow + 1 'Increment row number
next c
exit for 'exit column loop
end if
next i
