How to move to next iteration of a Do Until loop

[DannyJ]

Hi ALl,

I have some code that I have tried to write in a few diferent ways.

This is what I am trying to achieve:

Please Login or Register  to view this content.

I have also tried:

Please Login or Register  to view this content.

This still picks up the cells containing those words.

I also tried using GoTo and putting the reference above the new loop but then it says that there is a loop without a Do.

Any help much appreciated. Thank you in advance

[cytop]

I don't understand the logic as written but should ShiftLabel be assigned a value outside the loop ...?

Ignoring code for the moment, describe what you want to do.

[DannyJ]

Wow, I feel stupid. Thanks Cytop. Think I need to step away from the computer for a while.

[cytop]

I need to step away...

It's 10:00am. Slough must be in a different time zone to the rest of the UK

[DannyJ]

It is 10:00 am. I work shifts, started at 03:00 am. :P :/

[cytop]

(Gets big conical hat and stands in corner)

[DannyJ]

(Gets big conical hat and stands in corner)

I wish I could add rep again just for this response! Haha. Thanks for your help, I think the dunce hat should still be mine after my mistake to be fair!

[cytop]

You might find this makes the loop structure clearer...

Please Login or Register  to view this content.

The only addition is the Start row should be defined.

A couple of things to note:
Indent your control structures - it makes it a lot easier to see the logic flow. There are free utilities to do this. One of the best is MZTools (which has other functions as well). Any search will find it. You need MZ Tools 3.0 for VBA. It's so old it's being given away free but there is a paid for version as well.

Use 'Option Compare Text' (See Help or search). This makes string comparisons case insensitive and avoids the 'A' <> 'a' problem.

Use 'Option Explicit'. You will have to declare all variables before use and prevents undefined variables creeping in and causing hard to find bugs. Again, any basic search will find a load of info.

And finally - you might be surprised by the etymology of the word dunce. Just like 'awful' it meant something completely different before. Both have totally reversed in meaning. Awful originally meant 'awe inspiring' and 'dunce' is (allegedly) dereived from John Duns Scotus, considered to be one of the three most important philosopher-theologians of the 13^thc.

[DannyJ]

Thanks for the extra help cytop, I am still learning a lot. I bodge a lot of things together. I will see if I can download MZTools here.

This is the full code

Please Login or Register  to view this content.

The x and y are 2 more loops later on. Can I still use the case argument that you put in for the above code?

Y finds the column that it should be pasted into and x finds the next empty row after row 4 , so I assume I can use Case True and then pasteSpecial with cells(x,y) still?

Very interesting facts about Awful(I have always wondered about this as Awe and Full tend to be positive words). Did not know about Dunce though.

[cytop]

If you had the code as written originally, then the 'Select Case' statement would be an improvement but the combined IF ... OR statement is just as effective. No doubt someone might argue the Select Case is more efficient but whats 5 milliseconds here or there?

Nothing wrong with your code - but some comments/alternatives...

Please Login or Register  to view this content.

It has also been indented (MZ Tools) - so much easier to follow the logic.

[DannyJ]

That's amazing, thanks for your help. Good to know I am on the right tracks, just need to streamline what I do now.

I know about For loops but I struggle to work out when to implement them so thank you for your further explanation here.

Tried downloading MZTools but the download gets blocked, will have to see if there is a way. Now to go back and indent my other codes in the same way! 3 hours left so a nice easy finish to the shift.

Thanks also for taking the time to answer my further questions. Glad I made that mistake now!