+ Reply to Thread
Results 1 to 8 of 8

Code not looping correctly

  1. #1
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122

    Code not looping correctly

    Frustrated here as I had this code working then after adding a few extras found that it was not looping correctly. After trying to fix it I could still not find where the problem is. It is only running through the loop one time. I think the problem is the following code in bold near the bottom but I cannot work out how it should go.

    ActiveCell.Offset(20, 0).Select
    End If
    Next
    End With
    Else
    End If

    NTDS = MsgBox(


    Please Login or Register  to view this content.
    Last edited by Aussie_Striker; 01-27-2007 at 10:08 AM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello Aussie_Striker,

    The most obvious problems I see in the code are the DIM statements need to be moved to the top of the Sub before any other code. Eventhough FOR ... NEXT loops will work without the index following NEXT statement, this is a bad programming practice, If you make these 3 changes, it will most likely fix your problem.

    Sincerely,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122
    Quote Originally Posted by Leith Ross
    Hello Aussie_Striker,

    The most obvious problems I see in the code are the DIM statements need to be moved to the top of the Sub before any other code. Eventhough FOR ... NEXT loops will work without the index following NEXT statement, this is a bad programming practice, If you make these 3 changes, it will most likely fix your problem.
    Don't quite understand what that means. Do you mean it doesn't need the next but should have it? With the dim, possibly it should not be a dim? the nm being the name of the worksheet will change on each loop. Will that affect it if I move the dim to the top of the code?

  4. #4
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Hi Aussie,

    You only DIM a variable one time (unless it is an array and you are resizing it; in which case, you still only DIM it once, and thereafter you use REDIM).

    You can put a DIM statement anywhere in the code, as long as you put it before you actually use the variable (otherwise, it is dimensioned by VB at the point it is first used when the code is complied and you get a duplicate dimension error). By convention, people tend to put all of the DIM statements at the top of the code. I have never put a DIM statement inside a loop. I sort of agree with Leith that this could cause a problem. But, I just tested it in a little routine and ... surprised me ... it did not cause a problem.

    Move it outside the loop anyway. What the heck ... put it at the top of the code. But, after doing the test mentioned above, I do not believe this is causing the problem.

    As for the "next" statement. VB has no problem with a loop like this:
    Please Login or Register  to view this content.
    But, you and I might. The issue is that as soon as you start having loops within loops and a bunch of if statements interspersed, it becomes impossible to figure out which "For" goes with which "Next" ... for us humans. So, we tend to write the code like this:
    Please Login or Register  to view this content.
    We find it easier to read that way.

    Anyway, you are asking for help and all we have given you so far is gratuitous advice. Let me try to dig a bit deeper and see if I can figure out your problem.

    But ... to Leith's point ... the lack of consistent indenting, combined with the lack of tell using which For goes with which Next, and the occassional oddly-placed Dim statement, all add up to making this code very difficult to read! Which means we might not be able to provide help as easily as we would like to.

  5. #5
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Not at all sure, but my guess is that this line of code is the problem:
    Please Login or Register  to view this content.
    If my guess is correct, then you have 2 options for fiixing it.
    1. put a dot (.) in from of Cells
    Please Login or Register  to view this content.
    so that VB knows it is referring to
    Please Login or Register  to view this content.
    otherwise, the WITH statement is not doing anything for you that I can see.

    2. you did a very good job of referencing in this statement:
    Please Login or Register  to view this content.
    so, you could replace the line I think is causing you problems with
    Please Login or Register  to view this content.
    Let me know if that does not solve you problem and I'll look some more. Meanwhile a couple of other tips that might help make coding easier.

    When you have a MsgBox with vbYesNo, you can check the response like this (for example) … Instead of
    Please Login or Register  to view this content.
    Use
    Please Login or Register  to view this content.
    You DO NOT need an Else statement with every IF … END IF. If you are not going to put anything between ELSE and END IF, then skip the ELSE.

  6. #6
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122
    Thanks, I'll get onto those and see if it fixes it. I went through and indented the code how it should be. The majority of the code was working. I added the first and last messagebox to run extra bits and I think it was after that the main part failed. I was getting error with end if not being where it should. My biggest mistake was not having a backup save where that code was working and I had saved past that point. I'll see if what you have suggested above works. Thanks

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122
    It's working properly now. Thanks for the help. I'm sure it wont be too long before I find another problem...lol

  8. #8
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122

    Version 2

    Well the old code was working but after reworking it considerably and shortening it it is not currently working. The sheet was not used last season for an unrelated reason but will be used this season. The current code is not finding any sheets in the nominated folder for some reason. Ideally I would like someone to look at the actual file as it may be a lot easier to understand what is going on with it. I can upload it somewhere possibly?
    The code is going through the first check for clearing sheet then the 2nd check to generate the data sheets keeps coming up with

    Here is the code

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    Any help would be appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1