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
– David Zemens, Commented Nov 1, 2016 at 16:19
(FYI, it's recommended to avoid using .Select/.Activate) — BruceWayne
– BruceWayne, Commented Nov 1, 2016 at 16:32

Community · Accepted Answer · 2017-05-23 12:17:08Z

4

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

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Nov 1, 2016 at 16:32

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Bustos Over a year ago

Nice answer - You could even set output to 5 before you start the for loop and then get rid of the IIF()

David Zemens Over a year ago

@JohnBustos yes, you're right. I like that better. Revised.

Z117 Over a year ago

That is way better, thanks a lot. You just made my life easier ;)

John Bustos · Accepted Answer · 2016-11-01 16:31:27Z

0

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

answered Nov 1, 2016 at 16:31

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

2 Comments

Z117 Over a year ago

Thanks a lot! Makes sense this way :)

John Bustos Over a year ago

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

Pat Jones · Accepted Answer · 2016-11-01 16:48:33Z

0

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

answered Nov 1, 2016 at 16:48

Pat Jones

8989 silver badges21 bronze badges

Comments

user3598756 · Accepted Answer · 2016-11-01 17:30:57Z

0

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

answered Nov 1, 2016 at 17:30

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

VBA for-loop with three variables

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related