Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Endless Do While Loop (VBA-Excel 97)

    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.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Endless Do While Loop (VBA-Excel 97)

    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.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Endless Do While Loop (VBA-Excel 97)

    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.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Endless Do While Loop (VBA-Excel 97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •