Looping and indexing in VBA

Question

I have experience with C/C++ but I am new to VBA and Excel.
What I have is:

Range("A7:L7").Select
Selection.Copy
Range("R18").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    'One column copied

The problem is that I want to go through an entire range of cells (everything from A6:L6 all the way up to A41:41).

I tried looking into For loops but I don't understand exactly how indexing works when selecting ranges. Here's what I have written so far:

pasteLocation = 6
For i = 6 To 41
   Range("A" & i:"L" & i).Select

    Selection.Copy
    Range("R" & pasteLocation).Select '+12 every time to this counter

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    pasteLocation = pasteLocation + 12 'want to move down by 12 every time

Next i

Clearly I'm doing something wrong because I get "Compile Error: Expected: list separator or )"

Can anyone explain how indexing with VBA works and what I'm doing wrong?

[See this](http://stackoverflow.com/questions/5035399/iterating-100-cells-takes-too-long/5035528#5035528) — ARich, Oct 05 '13 at 18:10
When using VBA on ranges you should avoid using `select` See [this](http://stackoverflow.com/a/10717999/445425) for some examples — chris neilsen, Oct 05 '13 at 18:11

score 3 · Answer 1 · answered Oct 05 '13 at 17:46

3

You have a typo:

Range("A" & i:"L" & i).Select

should be

Range("A" & i & ":L" & i).Select

answered Oct 05 '13 at 17:46

Heinzi

167,459
57
363
519

score 1 · Answer 2 · answered Oct 05 '13 at 18:10

Heinzi has the answer.

You can tidy the code a little aswell. No need to mention default arguments. Also maybe the use of With adds some readability. I like to qualify most objects up to the class Excel; probably a bit over-the-top but certainly doesn't do any harm.

Option Explicit

Sub slightlyTidier()

Dim pasteLocation As Integer
pasteLocation = 6

Dim i As Integer
For i = 6 To 41

    With Excel.ActiveSheet
        .Range("A" & i & ":L" & i).Copy

        '+12 every time to this counter
        .Range("R" & pasteLocation).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    End With

        'want to move down by 12 every time
    pasteLocation = pasteLocation + 12
Next i

End Sub

user2780436 · Answer 3 · 2013-10-07T02:11:15.313

To make the code work, all you have to do is move the colon inside of the double quotes as Heinzi suggested. Since you are new to VBA I thought it best to give you a full solution. I removed the .Select .Copy and .PastSpecial methods as they are not required and can slow your code execution down substantially.

Here is what I consider to be an optimized solution:

Sub Solution()

    Dim pasteLocation As Integer
    pasteLocation = 6

    Dim rngFrom As Range
    Dim rngTo As Range

    For i = 6 To 41
        With Excel.ActiveSheet
            Set rngFrom = .Range("A" & i & ":L" & i)
            Set rngTo = .Range("R" & pasteLocation & ":R" & (pasteLocation + 12))
            rngTo.Value = rngFrom.Value
        End With
        pasteLocation = pasteLocation + 12
    Next i

End Sub

While this example is only set up to copy cell values, other attributes of the range could easily be included (ie. Formatting etc.).

+1 definitely more reliable to use this method which I think means that the clipboard doesn't come into it so simplifies things in the background. I've had problems before using `.Copy`. I wonder though if it's definitely quicker - I'll try to set up a small test to find out how similar they are on my PC. — whytheq, Oct 12 '13 at 11:32

Looping and indexing in VBA

3 Answers3