Outer loop not running in VBA

0
votes

I have little experience in script writting and I am trying to rearrange a large set of data in excel using VBA. The script only goes through the inner loop and does not run the outer loop. Any pointer as to why?

thanks!

Row = 6
Column = 5
Destinationrow = 6
Destinationcolumn = 19

Do While Column <= 16 And Destinationcolumn <= 30

    Do While Row <= 561 And Destinationrow <= 92

    ActiveSheet.Cells(Row, Column).Select
    Selection.Copy

    ActiveSheet.Cells(Destinationrow, Destinationcolumn).Select
    ActiveSheet.Paste

    Row = Row + 9
    Destinationrow = Destinationrow + 1

    Loop

Column = Column + 4
Destinationcolumn = Destinationcolumn + 1

Loop
2
excelvba

2 Answers

2
votes

It looks fine except that you do not reset your rows at the end of the inner loop. Maybe it should be:

Column = 5
Destinationcolumn = 19

Do While Column <= 16 And Destinationcolumn <= 30

    Row = 6
    Destinationrow = 6

    Do While Row <= 561 And Destinationrow <= 92

        ActiveSheet.Cells(Row, Column).Select
        Selection.Copy

        ActiveSheet.Cells(Destinationrow, Destinationcolumn).Select
        ActiveSheet.Paste

        Row = Row + 9
        Destinationrow = Destinationrow + 1

    Loop

    Column = Column + 4
    Destinationcolumn = Destinationcolumn + 1

Loop
0
votes

You have to initialize row loop variables inside the first loop:

Column = 5
Destinationcolumn = 19

Do While Column <= 16 And Destinationcolumn <= 30

    Row = 6
    Destinationrow = 6

    Do While Row <= 561 And Destinationrow <= 92

    ActiveSheet.Cells(Row, Column).Select
    Selection.Copy

    ActiveSheet.Cells(Destinationrow, Destinationcolumn).Select
    ActiveSheet.Paste

    Row = Row + 9
    Destinationrow = Destinationrow + 1

    Loop

Column = Column + 4
Destinationcolumn = Destinationcolumn + 1

Loop

Also you could replace the 4 copy and paste lines with just:

ActiveSheet.Cells(Destinationrow, Destinationcolumn) = ActiveSheet.Cells(Row, Column).Value