VBA for-loop with three variables

Question

I am quite new to VBA, but I am learning. I have a worksheet "Model" which has 18 tables. I defined their ranges with 'start' and 'end'. So as you can see in the VBA below, the first table is in C3:E13 and the last table in C224:E234. I want to copy these and paste them one by one in Sheet1.

There they have to be pasted in cells B5, B21, B38, ..., B166. So the first table should be pasted in B5, the second one in B21, etc.

So my question is, how can I create this variable 'output' (which defines the output rownumber) in my for-loop?

Dim start As Long
Dim eind As Long
Dim output As Long

For start = 3 To 224 Step 13
    end = start + 10

           'output = --->>> this should be 5, 21, 38, ..., 166. 
           'So something like output = 5 To 166 Step 16

Sheets("Model").Select
Range("C" & start & ":E" & end).Select
Selection.Copy
Sheets("Sheet1").Select
Range("B" & output).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next start

Many thanks in advance!

Are these actually *Tables* (e.g., from the Insert>Table ribbon)? — David Zemens, Nov 01 '16 at 16:19
(FYI, it's recommended to avoid using [`.Select`/`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)) — BruceWayne, Nov 01 '16 at 16:32

score 4 · Accepted Answer · edited May 23 '17 at 12:17

Note that 21 + 16 is 37, not 38 as per your comment. Not sure if that's a typo. Revised to avoid Select which is 99% unnecessary, and to avoid the relatively expensive Copy in favor of directly transferring values from one range to another.

Dim start As Long
Dim end As Long
Dim output As Long
Dim tbl as Range
Dim dest as Range

output = 5
For start = 3 To 224 Step 13
    end = start + 10
    Set tbl = Sheets("Model").Range("C" & start & ":E" & end)
    Set dest = Sheets("Sheet1").Range("B" & output).Resize(tbl.Rows.Count, tbl.Columns.Count)
    dest.Value = tbl.Value
    output = output + 16

Next

If your tables are proper tables which were created from Insert > Table, then you could do something like:

Dim tbl as ListObject
Dim t as Long
Dim dest as Range
For t = 1 to Sheets("Model").ListObjects.Count
    Set tbl = Sheets("Model").ListObjects(t)
    Set dest = Sheets("Sheet1").Range("B" & (5 + ((t - 1) * 16)))
    dest.Resize(tbl.Rows.Count, tbl.Columns.Count).Value = tbl.Value
Next

Nice answer - You could even set `output` to 5 before you start the for loop and then get rid of the `IIF()` — John Bustos, Nov 01 '16 at 16:58
That is way better, thanks a lot. You just made my life easier ;) — Z117, Nov 02 '16 at 18:24

score 0 · Answer 2 · answered Nov 01 '16 at 16:31

Although I would suggest doing things quite differently, let me answer your actual question as it will still work and is still a completely valid programming question.

The way to do this is to actually have a separate "counter" variable (I usually call it i from my old c++ college courses) and then increment start, end and output accordingly while in a loop statement. That would look as follows:

Dim i As Integer

Dim start As Long
Dim end_ As Long
Dim output As Long

i = 0
Do

    start = 3 + i * 13
    end_ = start + 10
    output = 5 + i * 16

    ' ... your code....

    i = i + 1
Loop While start <= 224

Basically, then you can set all the variables as you want and set your exit criteria as needed.

I hope that makes sense and works for you!!

Glad it makes sense and glad you got what you were looking for. I do believe, though, that you accepted the correct solution - @DavidZemens solution is definitely the best way to do this. This is just a different way to get to the same outcome and allows for more explicit variable setting — John Bustos, Nov 02 '16 at 18:54

score 0 · Answer 3 · answered Nov 01 '16 at 16:48

You could try something like this. Play around with the increments (e.g. the loop step value, the j + 10, i + 10, and j = j + 11) to get the spacing between your ranges and the height/width of your tables the way you want.

Public Sub copyTables()

Dim i As Integer, j As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

j = 5

For i = 3 To 224 Step 11
    ws2.Range("B" & j, "D" & j + 10).Value = ws1.Range("C" & i, "E" & i + 10).Value
    j = j + 11
Next i

Set ws1 = Nothing
Set ws2 = Nothing

End Sub

score 0 · Answer 4 · answered Nov 01 '16 at 17:30

you could try this:

Sub main()
    Dim iTab As Long

    With Worksheets("Model").Range("C3:E13")
        For iTab = 1 To 18
            Worksheets("Sheet1").Range("B5:D15").Offset((iTab - 1) * 16).Value = .Offset((iTab - 1) * 13).Value
        Next iTab
    End With
End Sub

VBA for-loop with three variables

4 Answers4