VBA loop exiting loop on second run

Question

I need some help with some VBA. The code below sorts a bunch of data which is spread horrizonally then apends them vertically which I have posted below:

Sub Test()

    Application.ScreenUpdating = False

    countrow = ActiveSheet.UsedRange.Rows.Count
    countcolumn = ActiveSheet.UsedRange.Columns.Count
    numberofiterations = countcolumn / 6

    MsgBox "Number of Rows is" & Str(countrow)
    MsgBox "Number of Column is" & Str(countcolumn)

    ActiveSheet.Select

    a = 1
    b = 1
    c = 6
    d = 1

    While n < numberofiterations

        Range(Cells(a, b), Cells(countrow, c)).Select
        Selection.Copy

        Sheets(2).Select
        ActiveSheet.Cells(d, 1).Select
        Sheets(2).Paste

        Sheets(1).Select

        b = b + 6
        c = c + 6
        d = d + countrow

        n = n + 1

    Wend

End Sub

It runs ok once but when running it for the second time it itteraits through to the line:

While n < numberofiterations

I can't find the reason why it drops out the loop the second time. Any help will be apriciated

Thanks,

add `n=0` (or `n=1` depends on your needs) before `While n < numberofiterations` — Dmitry Pavliv, Feb 04 '14 at 14:04
Also, you should check the post **[Excel macro - Avoiding using Select](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select)** to speed up your process and to make it more stable. — simpLE MAn, Feb 04 '14 at 14:42

score 1 · Accepted Answer · answered Feb 04 '14 at 14:46

A few things to consider:

1) Please initialize the value of n. That is, before you start your loop, set

n = 0

explicitly. If you later add other code that happens to set n to some value, you will not get the result you expect

2) When you say

countrow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(a, b), Cells(countrow, c)).Select

You will not get a selection all the way to the bottom of the range IF THE USED RANGE DIDN'T START IN ROW 1. If UsedRange = $Q1:Z20, then UsedRange.Rows.Count = 10, not 26!

This second point is probably not your problem today - but I wanted to point it out as it will bite you another time.

3) I am a huge fan of writing

Option Explicit

at the top of every module. It forces you to be thoughtful about every variable you create, and more likely will make you remember to initialize variables as well. In general it's good practice, and should be right up there on your list with "initialize right before you use".

I will have to force the user to use cell A1 somehow. With the `Option Explicit` can I just put that before the `Sub Moog()` and it will force all the vairiables — Tito Paterson, Feb 04 '14 at 15:43

VBA loop exiting loop on second run

1 Answers1