Here we go again with Loops

[TedX]

For those who know me, I suffer from CFOL (Cronic Fear Of Loops), please don't tell me they are the easiest thing ever, because the mere mention of the word numbs me into submission.

So having confessed (again), can someone please show me a VBA code that will loop through a horse race and colour in the highest three-scoring horses.

To make it easier for you, I need to tell you how it all works. Firstly in the image below, you can see Race 2 which has 18 acceptances, with horses 6, 8,10, 13 & 14 scratched from the event, therefore they are simply hidden rows, but they are still there of course. So every race I do has a different number of starters (Horses) and there is a different number of races per day. In the example, I have manually coloured in, in Green & Yellow the top three horses that have registered a score. There are only 4 horses running in this event that have a score, some races have every horse with a score, and some races have zero horses with a score. So you can see, everything changes, the things that never change are the things in the columns, the Horse names are always in Column B, and the scores are always in Colum S and there is never ever, going to be a race meeting that would exceed 300 runners, in fact, the maximum is 12 races of 24 runners for a total of 288 runners and the odds of that happening is larger than the number of Hydrogen atoms in the Universe. (so any loop doesn't need to go beyond 300 rows).

That's it, if I'm not clear, please ask me to rephrase some part. I can tell you that colouring in, 6 race meetings per day with an average of around 14 runners, takes me a long time and if I had a macro that just did it, it would do it in a couple of seconds. Thanks, heaps in advance

 

[Ezguy4u]

Ted, I grew up near Santa Anita Race Track, so naturally I have an excel spreadsheet that attempts to pick the winning horse. I have made a small fortune using this spreadsheet. The problem is I started out with a large fortune. It’s still fun to be a part of the horse racing family. Just a suggestion, why not sort the registered score from highest to lowest? The cream rises to the top and you don’t have to loop or color. Side bar, I have been in vba loops that didn’t end and had to shut off the computer to stop the loop. If you are still intent on looping, I have some questions. Do put all the races on one sheet? I see a 2 in row 25. Is that the second race? If that is the case, we need a way of separating the races. In either case we have some work to do here.

 

[TedX]

Do put all the races on one sheet? I see a 2 in row 25. Is that the second race?

Hi Ezguy4u, LOL, I have been hearing that sort of reducing your wealth for a very long time hahahahahah. Okay, yes that is Race 2, you are correct. Each race at a certain racetrack goes down the worksheet and follows the same structure it's a number as in 2 for Race 2, then a clear row, then the Race time, as in 13:40 which is 1.40 PM. Then another blank row, then the header row giving the TAB which is the horse's number in the race, the Horse which stands for name, there are about 15 or 16 hidden columns which I can expand if necessary that has other information, then in Column S, the score. Our champions get up to close to the 100 score whilst the '60s and 70's you see in the example are relatively poor horses, what you might call "Claimers".

I did have a loop working once before I totally destroyed it. I tried to make a couple of minor changes, that didn't work then tried to put it back the way it was and couldn't then tinkered a bit more until it was broken beyond repair. It basically had a loop and the macro had the word STOP written down in cell S301. The loop kept going until it hit the word STOP and then just finished. As the macro would do each race, I remember seeing that it would start at the top, go to the first space below the time, because each time was unique and would increase throughout the day, then drop down a couple of rows, then jump to the right by 18 or so columns and land in the score column, then go to the bottom of that race, select all the numbers, do Conditional Formating on them, then jump back to the left 19 columns (or so) until it hit column A, then repeat this working its way through each race. Going down, jumping to the right, doing the formatting, jumping to the left, going down and so on.

Please forgive me for what is possibly the worst description of a loop in the history of computing

 

[TedX]

Please forgive me for what is possibly the worst description of a loop in the history of computing

One thing I did forget to mention earlier is that every horse name is unique in a race, which might not sound Earth-shattering but the same horse name can appear in different races on the same day, these are considered reserves and are listed for several races and then scratched from the races, the reserves are not required for. I'm just mentioning this because it might be easier to use the horse names in a race to find the first and last horse name which will become the boundary of the race, if you can understand where I'm coming from.

 

[TedX]

I just wanted to report that I figured out an alternate method of accomplishing my task, without the use of the evil, dreadful loops Okay, I guess I'm overreacting and I'm sure loops are fine but I just have this thing about them. My solution is just a very long one but with today's very fast computers, it doesn't take too long to work through the macro.

Without doing a small amount of code over and over in a loop until a condition was met, I simply recorded myself manually doing the first race and then set about adding the code over and over, punctuated by some directional code that moved the cursor down to the start of every new race. So it was basically do a race, then navigate down to the next race, then do that race and so on. In some race meetings, I might only have 7 races but I still had to work on the understanding that on another day there could be 12 races, so I created some fake data to go onto the end of the last race and then did my solution code down to 12 races, tricking the macro into doing 12 races even when there were only 7 races.

All that might be hard to visualise with just my textual description but seeing I do 6 meetings per day, the one macro ran to a very long one but I ran it a few times and to my sheer delight, it only takes about 10 seconds, saving me about an hour. So, I'll mark this question as solved and I will go and rewatch, for about the hundredth time, all about loops again, because someday I want to overcome my phobia of them. Happy New Year to you all.