+ Reply to Thread
Results 1 to 14 of 14

Loop counter

  1. #1
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Loop counter

    Hi all!

    I have this code:
    Please Login or Register  to view this content.
    And I wonder if I could pop up a msgbox saying how many times the loop has been executed. Is this possible?

    Thank you in advance!
    Last edited by thorblow; 06-25-2021 at 05:26 PM.

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Loop counter

    Try it like this

    Please Login or Register  to view this content.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Loop counter

    What are you trying to do? Generally speaking, you don't have to select anything to perform an action on it. So, you don't need to select a sheet to delete a row in it. And you don't need to select a cell to make it the active row in order to delete it.

    Where have you got this code? It's certainly not a recorded macro.

    But, to answer your question, just define a variable and increment it every time you go through the loop. Remove the Exit Sub before the skip label, and put your MsgBox before the Exit Sub after the skip label.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Loop counter

    @maniacb: your MsgBox can never be executed … the code will never get there.

  5. #5
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Loop counter

    Hi TMS, I was about to answer the same to maniacb...

    Regarding the code, here I attach the Excel file. I just did it by my own and it does what I need... I'm not an expert of vba, I just look for some solutions on the internet and put it together :D

    Could you please rewrite the code adding the define a variable and increment it every time you go through the loop. Remove the Exit Sub before the skip label, and put your MsgBox before the Exit Sub after the skip label.? I can't do that on my own... :/
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Loop counter

    TMS, you're right. I put the Msgbox in the wrong spot. It should be after the line 'Loop'

  7. #7
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Loop counter

    Hi maniacb... I have just try it putting the msgbox after the line loop and still doesn't work... I have added the file so you can check it if you please...

    Thanks!

  8. #8
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Loop counter

    I think what you want to do is delete rows if the times don't match within 4 seconds. I wrote code that will do that instead of trying to get yours to work. Let us know if this is or is not what you want.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Loop counter

    Hi maniac! Thanks for this proposal, but please, note that yes, I delete the rows when the time difference is bigger than 4 seconds, but I delete the rows either in sheet datacam1 or in sheet datacam2, depending where is going to be the next time difference. I never delete the rows in sheet Test. I use the sheet test only as a work sheet for the macro itself and I expect to see the results in the sheets datacam1 and datacam2

  10. #10
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Loop counter

    Here is an option that bypasses the use of the test sheet. It deletes the smaller value line from datacam1 or datacam2 when the time difference is less than 4 seconds. I then see that the data is not aligned, but maybe that is what you want. Test it out. Let me know how it fares.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Loop counter

    Hello maniacb, sorry for my late reply!!

    No, this code is not doing the same as my code.

    The goal here is that DataCam1 and DataCam2 has some desynchronization and I need to remove all that values that they don't have a match. This means, that row B2 from DataCAm1 needs to have it's match in also row B2 in DataCam2 within 4 seconds max difference. When this condition is not meet, the time that doesn't have a direct match needs to be removed.

    In order to know the value to be removed, I calculate the time difference between time B2 and B3 of DataCam1 and then I compare it with the time difference of also cells B2 and B3 of DataCam2. If the time difference is more than 4 seconds, then deletes the lowest time.

    Of course my code needs the help of the formulas shown in the Sheet "Test" because I'm not that good to make all in one with just one code, but I'm happy to see that my code works and does precisely what I need, the only thing I would like to add is a loop counter to know how many rows I have deleted in total. And still would be more nice to know how many rows I have deleted in DataCam1 sheet and how many rows I have deleted in DataCam2.

    So, is there a way to add the loop counter as it is in my excel file or using a totally diferent code?

    Thanks again for your support!

  12. #12
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Loop counter

    OK, it was easier than I thought, I have just found the right code:

    Please Login or Register  to view this content.
    Now I get the result in cell J4 of 9 rows deleted Now my next challenge is to know how many of those 9 have been deleted in DataCam1 and how many in DataCam2... any ideas?

  13. #13
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Loop counter

    Try this option. J6 is CAM1 and J8 is CAM2.

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Loop counter

    Thanks a lot maniacb! It worked

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Loop counter
    By drgorantla in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2021, 02:55 AM
  2. [SOLVED] Counter and a Loop
    By bnevena in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-01-2018, 12:51 PM
  3. How to Reference a Loop Counter
    By Bensley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-18-2018, 03:19 AM
  4. For loop counter reset
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-03-2014, 10:59 AM
  5. [SOLVED] Counter in a loop
    By jonathanrgz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2013, 05:46 PM
  6. loop counter not recognised
    By Dave H9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2011, 02:53 AM
  7. How can I avoid changing a loop counter within a loop?
    By broro183 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2009, 07:59 PM

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