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



## TedX (Jan 3, 2023)

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 (Jan 3, 2023)

What about some sample data with XL2BB instead of expecting helpers to copy all that out to test?





TedX said:


> You may recall, we got every race to display the top three scores and colour them in green and yellow


No, I don't recall. Does that matter? Or perhaps you could provide a link.





TedX said:


> having remembered where it was will move to the next race


Could the Sheet have the word "Race" in cell U3 and each time the code could record the relevant race number in cell V3? (Or it could be stored elsewhere, which may be better because the row number could also be recorded)


----------



## TedX (Jan 3, 2023)

Peter_SSs said:


> What about some sample data with XL2BB instead of expecting helpers to copy all that out to test?
> 
> 
> 
> ...


Thank you Peter, using XL2bb and selecting Mini Sheet, rather than a table (not really sure of the difference), I'll add it here and YES by all means The spare U3 cell can be used for the purpose you suggested. 

XbetForm.xlsmV145VIC


----------



## TedX (Jan 3, 2023)

TedX said:


> Thank you Peter, using XL2bb and selecting Mini Sheet, rather than a table (not really sure of the difference), I'll add it here and YES by all means The spare U3 cell can be used for the purpose you suggested.
> 
> XbetForm.xlsmV145VIC


 That may not have worked so I'll try the Table:


----------



## Peter_SSs (Jan 3, 2023)

You need to *select the range you want to show* before clicking 'Mini sheet' - just like you did here not too long ago.


----------



## TedX (Jan 3, 2023)

Peter_SSs said:


> You need to *select the range you want to show* before clicking 'Mini sheet' - just like you did here not too long ago.


O I C  

XbetForm.xlsmABSTUVWXYZAA1xBetStake  25Horse #OddsProbabilityStakeProfit2Commission026.0016.67%$5.80$9.78352.7037.04%$12.88$9.7843 Horse Dutch75.5018.18%$6.32$9.78571.89%$25.0061813:3510TabHorse111CYNIC58.99122KHUMBU73.80133RODEO LEGEND53.41144TITANICUS37.92155TURNAWAY73.61166WALK UP START46.89177SILVER ONYX72.45188SH'BOURNE DE LAGO52.24199WOBURN ABBEY50.122010YAMBA'S STAR55.172122232422614:1028TabHorseXbet291FINE WEATHER63.15302MISTA RAZZLEDAZZLE65.30313PERSIAN BEAUTY58.16324HOMBRE GRANDE72.55335DARK SATIN59.04346EURELLYDIDIT62.72357DUBLE MEMORY61.62368SHADES OF FRISCO53.11379KARLU DREAMING19.883839NSWCell FormulasRangeFormulaY2:Y4Y2=IF(X2="","",1/X2)Z2:Z4Z2=IF(X2="","",(Y2/$Y$5)*$V$1)AA2:AA4AA2=IF(X2="",$V$1*(-1),((Z2*X2)-$V$1)*(1-$V$2))Y5:Z5Y5=SUM(Y2:Y4)S11:S20,S29:S37S11='C:\Xbet\[Xbet_NSW.xlsm]XbetCode'!$AA6Cells with Conditional FormattingCellConditionCell FormatStop If TrueS29:S37Cell Valuetop 3 valuestextNOS11:S20Cell Valuetop 3 valuestextNOS:SCell Value<60textNOX4Cell Value<2textNOX3Cell Value<2textNOX2Cell Value<2textNOY5Cell Valuebetween 0.45 and 0.65textNOS:SCell Value=50textNO


----------



## Peter_SSs (Jan 3, 2023)

Thanks, that makes it *much *easier (& it will generally get you faster/better results).

Try this with a *copy *of your workbook.


```
Sub Top3()
  Dim prevr As Long, r As Long, Num As Long, RaceNum As Long, lr As Long
  Dim HorseNums(1 To 3, 1 To 1) As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  prevr = Range("AD2").Value + 1
  r = Columns("A").Find(What:="Tab", After:=Cells(prevr, "A"), LookAt:=xlWhole).Row
  Range("W2:W4,V3").ClearContents
  If r > prevr Then
    RaceNum = Cells(r - 4, "A").Value
    Do While Num < 3 And r <= lr
      r = r + 1
      If Cells(r, "S").Interior.Color = 32768 Then
        Num = Num + 1
        HorseNums(Num, 1) = Cells(r, "A").Value
      ElseIf Cells(r, "A").Value = "Tab" Then
        Num = 0
        RaceNum = Cells(r - 4, "A").Value
      End If
    Loop
    If Num = 3 Then
      Range("W2:W4").Value = HorseNums
      Range("V3").Value = RaceNum
      Range("AC2:AD2").Value = Array(RaceNum, r)
    Else
      Range("AC2:AD2").ClearContents
      MsgBox "No more"
    End If
  Else
    Range("AC2:AD2").ClearContents
    MsgBox "No more"
  End If
End Sub
```

I have added "Race" to U3 and used V3 for the race number and AC1:AD2 as helpers. Those two helper columns could be elsewhere and could be hidden if required

Here is my sample data before the code has been run

TedX_1.xlsmABSTUVWABACAD1xBetStake  25Horse #RaceRow2Commission03Race43 Horse Dutch561813:3510TabHorse111CYNIC58.99122KHUMBU73.8133RODEO LEGEND53.41144TITANICUS37.92155TURNAWAY73.61166WALK UP START46.89177SILVER ONYX72.45188SH'BOURNE DE LAGO52.24199WOBURN ABBEY50.122010YAMBA'S STAR55.172122232422614:1028TabHorseXbet291FINE WEATHER63.15302MISTA RAZZLEDAZZLE65.3313PERSIAN BEAUTY58.16324HOMBRE GRANDE58.16335DARK SATIN58.16346EURELLYDIDIT58.16357DUBLE MEMORY58.16368SHADES OF FRISCO58.16379KARLU DREAMING19.883839404134314:1045TabHorseXbet461HORSE 164.22472HORSE 258.16483HORSE 358.16494HORSE 477.55505HORSE 559.04516HORSE 662.72527HORSE 761.6253Sheet1


After one code run:

TedX_1.xlsmABSTUVWABACAD1xBetStake  25Horse #RaceRow2Commission021173Race1543 Horse Dutch7Sheet1


After another code run:

TedX_1.xlsmABSTUVWABACAD1xBetStake  25Horse #RaceRow2Commission013513Race3443 Horse Dutch6Sheet1


After a third code run:


----------



## TedX (Jan 3, 2023)

Hi Peter, I tried everything, I worked on it for hours, but basically, it's not working. I can tell you what happened and what didn't happen. If I run the macro in step mode, nothing happens, it gets to the loop part and keeps looping forever. If I just run the subroutine (F5) it appears to run and finishes on the MsgBox, as you can see in the image below. The only visible thing that can be seen is the previous values of W2, W3 & W4 have been cleared. No values are entered into W2, W3 & W4, or U3, V3 or AC1:AD2. It's so close to working, I can feel it. It's as if it is just not storing the values before being asked to position them into the appropriate cells. Please don't give up on me, this is so close to working.  🙏 One thing I find very strange is that when I step through the code, in other macros, I can see the cursor moving to locate the correct cell but with this code, there is absolutely nothing yet, I can see that each line of code passes onto the next and then gets into the loop and just keeps looping, I hit the step button about a thousand times, thinking it might have gone down to the last race and been working its way back up but I gave up as my finger was red raw. I hope this info helps you. Oh yes, one last thing to tell you, in case the colours are throwing it for a loop, I use Conditional Formatting and for the interior green, the RGB is 0, 128, 0 or Hex #008000 and for the font colour yellow, I use RGB 255, 255, 0 or Hex #FFFF00 ~ good luck


----------



## Peter_SSs (Jan 3, 2023)

TedX said:


> in case the colours are throwing it for a loop, I use Conditional Formatting


That will be it. Way back in post #2 I said that I didn't know about the prior work regarding green/yellow in the cells & no link was provided.

Try this version instead. The only changes (two changes in one line) are highlighted.


```
Sub Top3()
  Dim prevr As Long, r As Long, Num As Long, RaceNum As Long, lr As Long
  Dim HorseNums(1 To 3, 1 To 1) As Long
 
  lr = Range("A" & Rows.Count).End(xlUp).Row
  prevr = Range("AD2").Value + 1
  r = Columns("A").Find(What:="Tab", After:=Cells(prevr, "A"), LookAt:=xlWhole).Row
  Range("W2:W4,V3").ClearContents
  If r > prevr Then
    RaceNum = Cells(r - 4, "A").Value
    Do While Num < 3 And r <= lr
      r = r + 1
      If Cells(r, "S")*.DisplayFormat*.Interior.Color = *RGB(0, 128, 0)* Then
        Num = Num + 1
        HorseNums(Num, 1) = Cells(r, "A").Value
      ElseIf Cells(r, "A").Value = "Tab" Then
        Num = 0
        RaceNum = Cells(r - 4, "A").Value
      End If
    Loop
    If Num = 3 Then
      Range("W2:W4").Value = HorseNums
      Range("V3").Value = RaceNum
      Range("AC2:AD2").Value = Array(RaceNum, r)
    Else
      Range("AC2:AD2").ClearContents
      MsgBox "No more"
    End If
  Else
    Range("AC2:AD2").ClearContents
    MsgBox "No more"
  End If
End Sub
```


----------



## TedX (Jan 3, 2023)

You are beyond genius, fixed!!!! We make a great team together  😂    😂 
What I can't understand is your thought process at the very beginning, I suppose it comes with experience after doing this for years and thousands of different issues.

For me, I read some people's issues and that's as far as I can get because I don't readily see the start, middle and end. I read the words and understand the description but thinking of the solution is the tricky part. Do you try to break it up into very small chunks and work up the code for each chunk, so to speak? Do you sit there for a moment and ask, "What does this person really want?" When you wrote this code for my issue and solved it (thank you very much), surely you have never seen an exact issue like mine because it's somewhat unique in the way I constructed it. So from my description alone, you grasped what I wanted to happen and then what did you think, did you think, along a tried and trusted pathway, like create variables, define the range, create a **** loop  sort out the way to end the loop, paste the values where they are meant to go., and so on? Do you actually think things out, or do you just start banging on the keyboard and it comes naturally because you have had 20 years or so, doing it?

I may seem nosy, but trust me, I'm sure there are thousands of people who would like to hear you say what you are thinking as you actually sit down to help someone. For people like me at the start of the road, it seems almost unimaginable that one day, I could be like you, but I'm sure that at some point, after you earn your stripes, it just all falls into place. At least I hope so. Regardless of how you do what you do, I for one am powerfully glad you and your colleagues do it. Thank you 🙏


----------



## TedX (Jan 3, 2023)

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 (Jan 3, 2023)

Glad it worked for you.



TedX said:


> 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.



TedX said:


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


Correct.



TedX said:


> .. 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 ..


TedX said:


> .. 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 (Jan 3, 2023)

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 (Jan 3, 2023)

TedX said:


> 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 (Jan 3, 2023)

Peter_SSs said:


> ```
> 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 (Jan 3, 2023)

You're welcome.



TedX said:


> Btw, yesterday I solved my first question by another member


Well done!


----------

