Search code examples
excelfor-loopcopy-pastevba

Excel Macro cut and paste cells with loop

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)

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.

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...

Thanks a lot in advance!

Solution

  • 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:

    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