vba Excel宏使用循环剪切和粘贴单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24171145/
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 Macro cut and paste cells with loop
提问by s.erhardt
I'm working with lots of data from a clinical study at the moment. For an analysis in SAS I have to change the the arrangement of the data that is stored in an excel table. This means hours of copy and paste if I'd do it manually, so I tried to write a macro. I spent hours on trying to figure out the code, I came up with a draft, of course it doesn't work since I don't have any backgrounds in VBA, so NO deeper understanding of the syntax. Although the problem is REALLY simple. Here's what I want to do (sry, just a link...since I can't post pictures yet)
我目前正在处理来自临床研究的大量数据。对于 SAS 中的分析，我必须更改存储在 excel 表中的数据的排列。这意味着如果我手动进行复制和粘贴需要数小时，所以我尝试编写一个宏。我花了几个小时试图找出代码，我想出了一个草稿，当然它不起作用，因为我没有任何 VBA 背景，所以对语法没有更深入的理解。虽然问题真的很简单。这就是我想要做的（对不起，只是一个链接......因为我还不能发布图片）
http://s7.directupload.net/images/140611/b96dhime.png
http://s7.directupload.net/images/140611/b96dhime.png
First the red arrow: cut the value from the top cell in column mmp1_v1 and paste it to the first cell of column mmp1. Then the blue one: cut the value from the top cell in column mmp1_v2 and paste it to the second cell from the top in column mmp1. Same thing with the next red and blue arrow. I have 194 rows in every column, 97 with values, 97 empty ones, so the process should be done within a loop, 97 times. All this for a total of 29 columns.
首先是红色箭头：从 mmp1_v1 列的顶部单元格中剪切值并将其粘贴到 mmp1 列的第一个单元格。然后是蓝色：从 mmp1_v2 列顶部的单元格中剪切值并将其粘贴到 mmp1 列顶部的第二个单元格。与下一个红色和蓝色箭头相同。我每列有 194 行，97 行有值，97 行是空行，所以这个过程应该在一个循环中完成 97 次。所有这些总共有 29 列。
The code I came up with - which obviously doesn't work - looks like this:
我想出的代码 - 显然不起作用 - 看起来像这样：
Sub cut_paste()
Dim nr As Integer
For nr = 1 to 195
Range("J&nr&").Select
Selection.Cut
Range("I&nr&").Select
ActiveSheet.Paste
Range("K&nr&").Select
Selection.Cut
nr = nr + 1
Range("I&nr&").Select
ActiveSheet.Paste
Next nr
End Sub
I wanted it to do like in Java kind of. I know...not the same language, but I just thought about defining a count variable called 'nr' and insert it into the string that stands for the range. But I don't understand the error messages, and this is definitely not the only problem. Sry for the missing proper terms...English isn't my mother tongue. I'd be SO glad if someone could try to write an example that kind of works the way I imagined. If you could even follow what I wanted to have. What I still had to do then would be altering the names of the ranges. Since there are another 29 columns that have to be processed in the same way. But still better than just copy and paste all of this manually I think...
我希望它像 Java 那样做。我知道......不是同一种语言，但我只是想定义一个名为“nr”的计数变量并将其插入代表范围的字符串中。但我不明白错误信息，这绝对不是唯一的问题。Sry 缺少适当的术语......英语不是我的母语。如果有人可以尝试编写一个像我想象的那样工作的例子，我会很高兴。如果你甚至可以遵循我想要的东西。我仍然需要做的是更改范围的名称。因为还有另外 29 列必须以相同的方式处理。但我认为仍然比手动复制和粘贴所有这些更好......
Thanks a lot in advance!
非常感谢！
采纳答案by MatthewHagemann
You have to handle strings differently in vba. You also need to lose the nr = nr + 1, as the next will increment it. See code below:
你必须在 vba 中以不同的方式处理字符串。您还需要失去 nr = nr + 1，因为下一个将增加它。见下面的代码：
Sub cut_paste()
Dim nr As Integer
For nr = 1 to 195
Range("J" & nr).Select
Selection.Cut
Range("I" & nr).Select
ActiveSheet.Paste
Range("K" & nr).Select
Selection.Cut
Range("I" & nr).Select
ActiveSheet.Paste
Next nr
End Sub
