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
– Dmitry Pavliv, Commented Feb 4, 2014 at 14:04
Also, you should check the post Excel macro - Avoiding using Select to speed up your process and to make it more stable. — simpLE MAn
– simpLE MAn, Commented Feb 4, 2014 at 14:42

Floris · Accepted Answer · 2014-02-04 14:46:14Z

1

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".

answered Feb 4, 2014 at 14:46

Floris

46.6k7 gold badges73 silver badges128 bronze badges

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

1 Comment

Tito Paterson Over a year ago

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

Collectives™ on Stack Overflow

VBA loop exiting loop on second run

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related