Increment two variables simultaneously in Excel VBA

Question

I have some some code like this in Excel VBA -

Sub Try()

    Range(Cells(2, 1), Cells(15, 1)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B2").Select
    ActiveSheet.Paste

    Sheets("Yasheet").Select



    Range(Cells(2, 2), Cells(15, 2)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B16").Select
    ActiveSheet.Paste

    Sheets("Yasheet").Select


    Range(Cells(2, 3), Cells(15, 3)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B30").Select
    ActiveSheet.Paste

    Sheets("Yasheet").Select


    Range(Cells(2, 4), Cells(15, 4)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B44").Select
    ActiveSheet.Paste

    Sheets("Yasheet").Select


    Range(Cells(2, 5), Cells(15, 5)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B58").Select
    ActiveSheet.Paste





    ActiveWorkbook.Save
End Sub

Now, there are two variables that I would obviously want to increment here to put the code elegantly and in a Loop.

Range(Cells(2, Counter1), Cells(15, Counter1)).Select ' From 1 to 5
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B" & Counter2).Select   ' From 2 , incremented by +14 in each iter
    ActiveSheet.Paste

    Sheets("Yasheet").Select

Now How Do i increment two variables ( Counter 1 & Counter 2 in this example ) Simultaneously in Excel VBA.

I remember seeing something similar for Javascript or Java , does VBA for Excel have something Similar ?

Thanks in Advance.

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248) — John Coleman, Jul 08 '18 at 19:20

score 3 · Answer 1 · answered Jul 08 '18 at 19:28

Something like this:

Sub Try()
    Const NUM_ROWS As Long = 14
    Dim n As Long, i As Long

    i = 2
    for n = 1 to 5

        Sheets("Yasheet").Cells(2, n).Resize(NUM_ROWS, 1).Copy _
                             Sheets("Sheet1").Cells(i, 2)

        i = i + NUM_ROWS 
    Next n

    ActiveWorkbook.Save
End Sub

score 3 · Answer 2 · answered Jul 08 '18 at 19:39

A little maths should be able to make the adjustments.

sub mytry()
    dim i as long

    with workSheets("Yasheet")
        for i=1 to 5
            .Range(.Cells(2, i), .Cells(15, i)).copy _
              destination:=workSheets("Sheet1").cells(2 +(i-1)*14, "B")
        next i
    end with

end sub

Increment two variables simultaneously in Excel VBA

2 Answers2