Excel VBA：粘贴问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20485791/
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
Excel VBA: Paste problems
提问by lizard053
I have tried a variety of ways to do this paste, but none of them are working. I am extremely new to programming, so I need some help with understanding why I keep getting either error 1004 or 5. I don't even understand what these errors mean.
我尝试了多种方法来做这个粘贴，但都没有奏效。我对编程非常陌生，所以我需要一些帮助来理解为什么我不断收到错误 1004 或 5。我什至不明白这些错误的含义。
Cells(hotcell).Copy
Cells.Offset(0, 1).PasteSpecial
or ...Paste, ...PasteSpecial = xlpasteall, ...pastespecial Paste:= xlpasteall, Range(Cells("B" & i)).paste, Range("B" & i).paste, and so on as above.
或 ...Paste, ...PasteSpecial = xlpasteall, ...pastespecial Paste:= xlpasteall, Range(Cells("B" & i)).paste, Range("B" & i).paste,等等如上。
I'm at a total loss. Everything else in the program is working just fine. I just can't get it to paste my copied values into the desired cells (all offset by a certain number of columns, but in the same row). Help and explanation both appreciated.
我完全不知所措。程序中的其他一切都运行良好。我只是无法将我复制的值粘贴到所需的单元格中（全部偏移一定数量的列，但在同一行中）。帮助和解释都表示赞赏。
EditThanks to BOTH of the answers I recieved, I was able to solve my problem. I really couldn't find a good answer anywhere I looked. Thank you!
编辑感谢我收到的两个答案，我能够解决我的问题。我真的在任何地方都找不到好的答案。谢谢！
The solution I used was one of the simplest:
我使用的解决方案是最简单的解决方案之一：
rng.Offset(0, 1) = rng.Text
Thanks again to the posters who answered, and the ones who commented. I was making it far too difficult.
再次感谢回答的发帖人和评论的人。我做得太难了。
回答by Jerome Montino
There are many ways to approach this kind of problem so I'll try to list some of the ones I use.
有很多方法可以解决此类问题，因此我将尝试列出我使用的一些方法。
No-paste approach
无粘贴方法
Sub CP1()
'This basically just transfers the value without fuss.
Dim Rng As Range
Set Rng = Range("A1")
Rng.Offset(0,1) = Rng.Value
End Sub
Simple paste approach
简单的粘贴方法
Sub CP2()
'This copies a cell exactly as it is.
Dim Rng As Range
Set Rng = Range("A1")
Rng.Copy Rng.Offset(0,1) 'Read: Copy Rng to Rng.Offset(0,1).
Application.CutCopyMode = False
End Sub
Special paste approach
特殊粘贴方式
Sub CP3()
'This copies the format only.
Dim sRng As Range, tRng As Range
Set sRng = Range("A1")
Set tRng = sRng.Offset(0, 1)
sRng.Copy
tRng.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub
Try determining from the three above which it is you want and modify accordingly. ;)
尝试从以上三个中确定您想要的并相应地进行修改。;)
Hope this helps.
希望这可以帮助。
回答by L42
Explanation:
解释：
Cells.Offset(0,1).PasteSpecial
This will give Error 1004since Cellsrefer to the entire sheet range and there is no way for you to offset it.
这将给出Error 1004因为Cells参考整个工作表范围并且您无法抵消它。
Cells(hotcell).Copy
This will give you the Error 5if the value of hotcellis not numeric.
I think Cellsonly accepts numeric argument if you used above syntax.
Error 5如果 的值hotcell不是数字，这将为您提供。如果您使用上述语法，
我认为Cells只接受数字参数。
How to use cells:(Excel 2007 and up versions)
如何使用单元格：（Excel 2007 及以上版本）
1.Define R,C syntax:Cells(RowNumber, ColumnNumber)
1.定义R,C语法：Cells(RowNumber, ColumnNumber)
Cells(1,1) 'refers to Range("A1")
Cells(1,2) 'refers to Range("B1")
Cells(2,1) 'refers to Range("A2")
2.Use a number only
2.只用一个数字
Cells(1) 'refers to Range("A1")
Cells(2) 'refers to RAnge("B1") and so on
Cells(16385) 'refers to Range("A2")
3.Using Cells only
3.仅使用单元格
Cells.Copy 'copies the whole range in a sheet
Cells.Resize(1,1).Copy 'copies Range("A1")
Cells.Resize(1,1).Offset(0,1).Copy 'copies Range("B1")
Cells.Resize(2,1).Copy 'copies Range("A1:A2")
4.Using numbers and letters (This only works on Cells(RowNum, ColNum)syntax)
4.使用数字和字母（这只适用于Cells(RowNum, ColNum)语法）
Cells(1, "A").Copy 'obviously copies A1
Cells(1, "A").Resize(2).Copy 'copies A1:A2
Now, how to copy and paste.
Suppose you want to copy A1:A5 and paste to the next column which is B.
现在，如何复制和粘贴。
假设您要复制 A1:A5 并粘贴到下一列 B。
Cells.Resize(5,1).Copy Cells.Resize(5,1).Offset(0,1)
The above will work because you Resizethe Cellsfirst before you do the Offset.
The values of A1:A5 will now be copied to B1:B5.
Hope this helps you.
以上将起作用，因为您Resize在Cells执行Offset.
A1:A5 的值现在将复制到 B1:B5。
希望这对你有帮助。
