Recognize a pattern, then take an action was solved, now the next step, real magic!

[TedX]

Okay, so with the help of some very knowledgeable people I was able to get my previous question, which dealt with dreaded loops, solved, in fact, I got 3 solutions

So now, in the interests of 100% pure laziness and never wanting to hit a keystroke ever again, I bring you all, the next step, which if solved (er, I mean, when solved) will be one step closer to perfection

If you take a look at the image, and ignore all the arrows, you will see how the active worksheet currently is, on a random day, random racetrack, and displaying races 6 and 7. You may recall, we got every race to display the top three scores and colour them in green and yellow, if three selections existed. All that works perfectly. Now I want to grab the corresponding number (TAB) of the three selected horses and enter them into the area indicated by arrows. So in Race 6, the numbers are 2, 4 & 7 and I wanted them pasted or entered into W2, W3 & W4. Looking down, you will see that Race 7 has three different numbers in 1, 2 & 9. I assume there will be no way around having to solve this other than using a loop. Don't forget, there is a different number of races every day, but everything always starts at cell A6, which is always Race 1. The Horse # always goes into Column W. Now here is the problem, and trust me, only certified magicians need read further

First Issue: Race 1 may not have 3 selections, therefore, the code should move on to the next race and only when it finds 3 selections in a race, then copy and enter those 3 numbers into the position in Column W.
Second Issue: You can only have the first race encountered on the worksheet, entered into column W, then after that race finishes, I would like to click a button that says 'Next Race', then the code having remembered where it was will move to the next race and search for 3 selections (scores), until it finds a race or ends. Okay, if it's just impossible, tell me straight up, I know it's hard but at the heart of it, it's just a copy and paste and some method of keeping track of which race was the last race done before resuming. If you can do this for me, it would be amazing. Seriously, AMAZING!!!!

 

[Peter_SSs]

Glad it worked for you.

Do you sit there for a moment and ask, "What does this person really want?"

Yes, because often the wording and/or examples are not entirely clear to somebody who is not familiar with the data or requirement.

.. surely you have never seen an exact issue ..

Correct.

.. would like to hear you say what you are thinking as you actually sit down to help someone.

Not something that is easy to put into a few words/sentences and it varies by the particular question. I guess it mostly comes down to ..

.. it comes with experience after doing this for years and thousands of different issues.

.. and looking at, and learning from, other people's great answers in the forum over the years.

 

[TedX]

Brilliant answers, thanks Peter. Btw, I created a button, so that each time I click it, it progresses to the next race. I'm just wondering what the code would be for a reset button, to start over if I needed it?

 

[Peter_SSs]

I'm just wondering what the code would be for a reset button, to start over if I needed it?

Sub Top3_Reset()
  Range("V3,W2:W4,AC2:AD2").ClearContents
End Sub

 

[TedX]

Sub Top3_Reset()
  Range("V3,W2:W4,AC2:AD2").ClearContents
End Sub

Yes that's perfect, thanks again Peter. Btw, yesterday I solved my first question by another member, It was probably more luck than any skill, but I did make a promise that I would pay it forward and that was the first step in doing so. Have a great day

 

[Peter_SSs]

You're welcome.

Btw, yesterday I solved my first question by another member

Well done!