+ Reply to Thread
Results 1 to 11 of 11

While, Wends, Ifs and Ors with unexpected behaviors

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    UK
    MS-Off Ver
    Office 2011 (Mac)
    Posts
    11

    While, Wends, Ifs and Ors with unexpected behaviors

    Hi

    I have some data in one sheet. I have some code that examines a specific column for specific strings; if the string is found then the data is cut to a new sheet. I have been testing this and it has been fine - or at least i thought it had.

    A user has come back and explained that only 'some' of the matches strings are being cut to the relevant sheet - and sure enough they are right.

    I have no idea why! Can anyone help?

    Please Login or Register  to view this content.

  2. #2
    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
    37,317

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    When you are deleting or cutting rows, you need to start at the bottom so that, when you remove a row it doesn't screw up Excel's internal position.

    Determine the last row of data and then work up from there (step -1) in your loop.

    Regards, TMS

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Avoid 'activate' and 'Select' in VBA.

    In Office2000 the while...wend has been replaced by the more flexible Do...Loop. You can choose where to introduce the while/until condition.

    This code will probably produce the same results:

    Please Login or Register  to view this content.
    Last edited by snb; 05-01-2013 at 05:27 AM.



  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Hard to know exactly without having a copy of the workbook but my guess would be that you cut the row if you find the data and then still incrememnt the counter, so if you had 3 rows:
    1 Warr016
    2 Warr02
    3 Warr016
    When Row 1 is cut, it would become
    1 Warr02
    2 Warr016
    And your counter would be at row 2, so the Warr02 term would be skipped

    So instead of
    Please Login or Register  to view this content.
    You need

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-19-2013
    Location
    UK
    MS-Off Ver
    Office 2011 (Mac)
    Posts
    11

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Thanks to all for suggestions.

    TM Shucks - i think that is the problem (yudlugar said similar) but alas I am not sure how to modify the code to start from the bottom up?

    snb - alas I tried your code and was presented with a 'Run Time Error 1004' - AutoFilter method of range class failed - any ides why?

    yudlugar - i tried your code too - but it seemed to stop after only checking one row - and i don't know why it did that either! (Can you tell i don't know very much?!)

    I attach the sheet in the hope that it makes things easier to debug.

    Inverness test.xlsx

    I want to look at column B and extract WARR16 and WARR02 to the Ford sheet, WARR01 to Iveco Warranty, WARR11 to Isuzu Truck, and Warr13 to Isuzu 4WD.

    Thanks to everyone for help so far.

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Why don't you use the first 2 rows ?

  7. #7
    Registered User
    Join Date
    04-19-2013
    Location
    UK
    MS-Off Ver
    Office 2011 (Mac)
    Posts
    11

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Hi snb - thanks for the reply. This macro is number 7 in a series of 15 that run to achieve a bigger goal - the first 2 rows need to be left as they are at this stage. Thanks

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Yeah I'm not sure that is the problem anymore, when I tried it on your worksheet the cut command left the row blank so you still needed to increment the counter. This seems to work on your worksheet:
    Please Login or Register  to view this content.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    What is the bigger goal?

    Are all the macros pretty similar?
    If posting code please use code tags, see here.

  10. #10
    Registered User
    Join Date
    04-19-2013
    Location
    UK
    MS-Off Ver
    Office 2011 (Mac)
    Posts
    11

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Yudlugar - that worked a treat! Star already clicked!

    Thanks SO much for your (and everyones!) help.

  11. #11
    Registered User
    Join Date
    04-19-2013
    Location
    UK
    MS-Off Ver
    Office 2011 (Mac)
    Posts
    11

    Re: While, Wends, Ifs and Ors with unexpected behaviors

    Norrie - the bigger goal has been the stuff of my nightmares! All of the macros are sound, except this one - All sorted now though - thanks.

+ 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