Camone
Camone

Reputation: 13

While-loop crashes Excel

I've been experiencing problems with my Excel crashing when performing a simple while loop using Visual Basic. The idea is that I want to select and copy all the rows in a table that belong to a certain 'batch' which is defined in the first column

Sub Copy5Cells()
lColumn = Range("C7").End(xlToRight).Column
StartRow = ActiveCell.Row
Batch = Cells(StartRow, 1).Value
ControlBatch = Batch
MsgBox ControlBatch


Do Until (ControlBatch <> Batch)
nextRow = StartRow + 1
ControlBatch = Cells(nextRow, 1).Value
Loop

num_rows = (nextRow - StartRow)
Range(ActiveCell, ActiveCell.Offset(num_rows, (lColumn - 2))).Copy
End Sub

here's an image of what the table looks like in Excel

Upvotes: 1

Views: 100

Answers (1)

simon at rcl
simon at rcl

Reputation: 7344

Each time the DO UNTIL loop goes round and starts again, it sets nextRow to StartRow + 1. However, StartRow always has the same value, so nextRow will also always have the same value.

Just before the LOOP statement, add a line to increment StartRow:

Do Until (ControlBatch <> Batch)
    nextRow = StartRow + 1
    ControlBatch = Cells(nextRow, 1).Value
    StartRow = StartRow + 1
Loop

Alternatively, set the initial nextRow before the loop starts and increment it just before the loop:

nextRow = StartRow + 1
Do Until (ControlBatch <> Batch)
    ControlBatch = Cells(nextRow, 1).Value
    nextRow = nextRow + 1
Loop
num_rows = (nextRow - StartRow) - 1

Upvotes: 2

Related Questions