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]

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

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

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

1. What about some sample data with XL2BB instead of expecting helpers to copy all that out to test?
   
   
2. 
   No, I don't recall. Does that matter? Or perhaps you could provide a link.
   
   
3. 
   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)

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.xlsm
	V
145
VIC

 

[TedX]

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.xlsm
	V
145
VIC

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

 

[Peter_SSs]

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

 

[TedX]

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.xlsm
	A	B																	S	T	U	V	W	X	Y	Z	AA
1		xBet																			Stake	25	Horse #	Odds	Probability	Stake	Profit
2																					Commission	0	2	6.00	16.67%	$5.80	$9.78
3																							5	2.70	37.04%	$12.88	$9.78
4																					3 Horse Dutch		7	5.50	18.18%	$6.32	$9.78
5																									71.89%	$25.00
6	1
8	13:35
10	Tab	Horse
11	1	CYNIC																	58.99
12	2	KHUMBU																	73.80
13	3	RODEO LEGEND																	53.41
14	4	TITANICUS																	37.92
15	5	TURNAWAY																	73.61
16	6	WALK UP START																	46.89
17	7	SILVER ONYX																	72.45
18	8	SH'BOURNE DE LAGO																	52.24
19	9	WOBURN ABBEY																	50.12
20	10	YAMBA'S STAR																	55.17
21
22
23
24	2
26	14:10
28	Tab	Horse																	Xbet
29	1	FINE WEATHER																	63.15
30	2	MISTA RAZZLEDAZZLE																	65.30
31	3	PERSIAN BEAUTY																	58.16
32	4	HOMBRE GRANDE																	72.55
33	5	DARK SATIN																	59.04
34	6	EURELLYDIDIT																	62.72
35	7	DUBLE MEMORY																	61.62
36	8	SHADES OF FRISCO																	53.11
37	9	KARLU DREAMING																	19.88
38
39
NSW

Cell Formulas
Range	Formula
Y2:Y4	Y2	=IF(X2="","",1/X2)
Z2:Z4	Z2	=IF(X2="","",(Y2/$Y$5)*$V$1)
AA2:AA4	AA2	=IF(X2="",$V$1*(-1),((Z2*X2)-$V$1)*(1-$V$2))
Y5:Z5	Y5	=SUM(Y2:Y4)
S11:S20,S29:S37	S11	='C:\Xbet\[Xbet_NSW.xlsm]XbetCode'!$AA6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S29:S37	Cell Value	top 3 values	text	NO
S11:S20	Cell Value	top 3 values	text	NO
S:S	Cell Value	<60	text	NO
X4	Cell Value	<2	text	NO
X3	Cell Value	<2	text	NO
X2	Cell Value	<2	text	NO
Y5	Cell Value	between 0.45 and 0.65	text	NO
S:S	Cell Value	=50	text	NO

 

[Peter_SSs]

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.xlsm
	A	B																	S	T	U	V	W					AB	AC	AD
1		xBet																			Stake	25	Horse #						Race	Row
2																					Commission	0
3																					Race
4																					3 Horse Dutch
5
6	1
8	13:35
10	Tab	Horse
11	1	CYNIC																	58.99
12	2	KHUMBU																	73.8
13	3	RODEO LEGEND																	53.41
14	4	TITANICUS																	37.92
15	5	TURNAWAY																	73.61
16	6	WALK UP START																	46.89
17	7	SILVER ONYX																	72.45
18	8	SH'BOURNE DE LAGO																	52.24
19	9	WOBURN ABBEY																	50.12
20	10	YAMBA'S STAR																	55.17
21
22
23
24	2
26	14:10
28	Tab	Horse																	Xbet
29	1	FINE WEATHER																	63.15
30	2	MISTA RAZZLEDAZZLE																	65.3
31	3	PERSIAN BEAUTY																	58.16
32	4	HOMBRE GRANDE																	58.16
33	5	DARK SATIN																	58.16
34	6	EURELLYDIDIT																	58.16
35	7	DUBLE MEMORY																	58.16
36	8	SHADES OF FRISCO																	58.16
37	9	KARLU DREAMING																	19.88
38
39
40
41	3
43	14:10
45	Tab	Horse																	Xbet
46	1	HORSE 1																	64.22
47	2	HORSE 2																	58.16
48	3	HORSE 3																	58.16
49	4	HORSE 4																	77.55
50	5	HORSE 5																	59.04
51	6	HORSE 6																	62.72
52	7	HORSE 7																	61.62
53
Sheet1

After one code run:

TedX_1.xlsm
	A	B																	S	T	U	V	W					AB	AC	AD
1		xBet																			Stake	25	Horse #						Race	Row
2																					Commission	0	2						1	17
3																					Race	1	5
4																					3 Horse Dutch		7
Sheet1

After another code run:

TedX_1.xlsm
	A	B																	S	T	U	V	W					AB	AC	AD
1		xBet																			Stake	25	Horse #						Race	Row
2																					Commission	0	1						3	51
3																					Race	3	4
4																					3 Horse Dutch		6
Sheet1

After a third code run:

 

[TedX]

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]

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]

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