CODEWITHSUNDEEP
vbaexcel
user2850099
user2850099

Reputation: 73

Looping and indexing in VBA

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?

Upvotes: 3

Views: 3964

Answers (3)

user2780436
user2780436

Reputation: 614

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

Upvotes: 1

whytheq
whytheq

Reputation: 35557

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

Upvotes: 1

Heinzi
Heinzi

Reputation: 172200

You have a typo:

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

should be

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

Upvotes: 3

Related Questions