Endless Do While Loop (VBA-Excel 97)

Topic

New Reply

What am I missing? This code executes fine moving the data as needed … but loops endlessly …
I’d appreciate any suggestions from someone with more expertise than I have. Thanks in advance.

Sub ReorganizeData()
‘A Routine that evaluates the point range
‘then copies the data to another workseet
Dim Count as Integer
Do while ActiveCell “”
If ActiveCell <= 10 Then
count = 1
Do While ActiveCell <= 10
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B1").Select
ActiveCell.End (xlDown).Select
ActiveCell.Offset(Count,0).Range("A1").Select
ActiveSheet.Paste
Count = Count + 1
Sheets("Macro Testing").Select
ActiveCell.Offset(1, -1).Range("A1").Select
Loop

ElseIf ActiveCell <= 20 Then
Count = 1
Do While ActiveCell <= 20
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C1").Select
ActiveCell.End (xlDown).Select
ActiveCell.Offset(Count,0).Range("A1").Select
ActiveSheet.Paste
Count = Count + 1
Sheets("Macro Testing").Select
ActiveCell.Offset(1, -1).Range("A1").Select
Loop

ElseIf ActiveCell <= 30 Then
Count = 1
Do While ActiveCell <= 30
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("D1").Select
ActiveCell.End (xlDown).Select
ActiveCell.Offset(Count,0).Range("A1").Select
ActiveSheet.Paste
Count = Count + 1
Sheets("Macro Testing").Select
ActiveCell.Offset(1, -1).Range("A1").Select
Loop

ElseIf ActiveCell <= 40 Then
Count = 1
Do While ActiveCell <= 40
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("D1").Select
ActiveCell.End (xlDown).Select
ActiveCell.Offset(Count,0).Range("A1").Select
ActiveSheet.Paste
Count = Count + 1
Sheets("Macro Testing").Select
ActiveCell.Offset(1, -1).Range("A1").Select
Loop
'etc. etc. etc. There will be more
End If

Loop

End Sub

Again …. thank you in advance for your expertise.

Reply | Quote

Replies

It is going to be impossible to tell without seeing what is on the worksheet. Your While loops are controlled by the contents of cells on the worksheet, so you can’t tell why it would loop endlessly without seeing what is in those cells.

I can take one guess however. Near the end of each section of While loops the macro looks like the macro may be switching the active sheet from whatever was active when the macro started to the sheet named “Macro Testing”. This switches the control of the loop from whatever was the active sheet when the macro started to the sheet “Macro Testing” in the middle of the loop. This is most likely not what you want to do.

I would also make a few comments:

1- Having code loop dependant on the content of cells on the sheet, without any other way to break out of the loop is not usually a good idea. If you do that, you can end up in an endless loop because the sheet does not contain what you expected it to.

2- This macro is uses the ActiveCell for most operations, and keeps switching the selection around. This is a very inefficient way to do things. It is almost never necessary to change the selection in a macro, and that should be avoided whenever possible. This is also going to cause the screen to flash insessently while the macro is running as each selection change will cause the screen to shift to a new cell.

Reply | Quote

Thanks for the response Legare. I forgot the worksheet at work last nite. Will bring it home tonite. The code starts on Macro Testing copies data based on the do loop selection to a place on the second worksheet. Yes, it does flash, but that is what we need it to do. Still more effective to do then by hand. I have a couple of other idea I thought about last nite that I will try today. It would be better if I could identify a block to move. I’m going to play with that today. The worksheet will contain over 4,000 rows with 47 incremental ranges to move. Thanks again.

Reply | Quote

Legare ….
I am proud to say, I came up with another approach that works so much better. I can accomplish the same thing using AutoFilter with the criteria that needs to move, select, copy, and paste it to the other work sheet … the reset the AutoFilter for the next set of criteria, select it, copy and paste and so on. It’s much cleaner. Thanks for your help.

Reply | Quote