While, Wends, Ifs and Ors with unexpected behaviors

[virgincinboy]

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.

[TMS]

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

[snb]

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.

[ragulduy]

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.

[virgincinboy]

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.

[snb]

Why don't you use the first 2 rows ?

[virgincinboy]

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

[ragulduy]

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.

[Norie]

What is the bigger goal?

Are all the macros pretty similar?

[virgincinboy]

Yudlugar - that worked a treat! Star already clicked!

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

[virgincinboy]

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.