+ Reply to Thread
Results 1 to 10 of 10

Why is my loop infinite, and how can I fix it?

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    Lincoln, NE
    MS-Off Ver
    Excel 2010
    Posts
    11

    Why is my loop infinite, and how can I fix it?

    I've started learning VBA from scratch this week, but I've been given a task at my internship that's stumping me. I'm trying to match information in two different worksheets in a common column, compare the two rows, highlight cells with differences, and then copy any rows that have differences to a different worksheet. Here is the part of the code that I believe is creating an infinite loop:

    Please Login or Register  to view this content.
    I'm just taking an initial stab at this, but I'd like to know why the loop is infinite and how I might be able to fix it.

    I am also aware that, if there are multiple differences in a single row this could create more problems. Let's assume for now that each row only has 0 or 1 differences.

    Thanks

  2. #2
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Why is my loop infinite, and how can I fix it?

    I would think it's because you have 3 nested loops inside of a loop and therefore there is a lot of time needed to process. Also, I'm not really sure where 'i' has been declared, I see you use it
    Please Login or Register  to view this content.
    but I don't see a value placed to it anywhere. Can you supply the whole procedure instead of part of it?
    EF killed Mordred

    46 & 2 is just ahead of me!

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Why is my loop infinite, and how can I fix it?

    There's nothing in that code that would cause an infinite loop. However, you do have a 4 loops nested together, so its probably just slow. Try running it on a small dataset (like 10 rows) so you can manually determine what the results should be and see if the macro gives expected results on the small dataset. If it does, then your code just needs to run for a long time to complete on your actual dataset.

    Instead of using so many For Loops, i'd recommend getting familiar with the Range.Find method.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Why is my loop infinite, and how can I fix it?

    Are you sure it's infinite? Looking at your code I don't see any while or do loops, therefore it should eventually finish. However, if you have large ranges you're checking, it may take a couple minutes to complete the process.

    Try adding to see how many iterations j goes through;

    Please Login or Register  to view this content.

    **Lol - all our above posts are prety spot on similar **
    Last edited by Medpack; 07-12-2012 at 06:28 PM.

  5. #5
    Registered User
    Join Date
    05-22-2012
    Location
    Lincoln, NE
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Why is my loop infinite, and how can I fix it?

    Thanks everyone for the quick responses. I'm back home now so I won't have access to the excel files until tomorrow morning. I'll take a look at it again then and post here if necessary.

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Why is my loop infinite, and how can I fix it?

    You can also add a progress meter to show you the percentage completed and the estimated time remaining.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  7. #7
    Registered User
    Join Date
    05-22-2012
    Location
    Lincoln, NE
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Why is my loop infinite, and how can I fix it?

    Good catch FortySixAndTwo, I had previously been using different variables for this code and just forgot to change that part. Thanks.

    Also, I like your signature

  8. #8
    Registered User
    Join Date
    05-22-2012
    Location
    Lincoln, NE
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Why is my loop infinite, and how can I fix it?

    Alright, so I've modified the code a bit. I got it to work this way, having the program only look at a single column (I'm only running 42 iterations to make it go faster for now):
    Please Login or Register  to view this content.
    But I'm not sure how to get the code to iterate through each cell in a row once a difference is found, aside from writing a duplicate line for each cell in the row...I've tried this :

    Please Login or Register  to view this content.
    and it runs without error, but none of the rows with differences are being copied over.

  9. #9
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Why is my loop infinite, and how can I fix it?

    Is there any way you can upload a work book (without sensitive or private data) to the forum? It will most likely help your cause if you do.

  10. #10
    Registered User
    Join Date
    05-22-2012
    Location
    Lincoln, NE
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Why is my loop infinite, and how can I fix it?

    That's the problem, I'm working with confidential info and, being an intern, I'm not sure what I can and cannot do regarding uploading things to the internet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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