VBA code/Macro help to count the distances between numbers on a roulette wheel

[JanusValeri]

Hi all,
I could use some help. I am creating a predictor for roulette and I am trying to figure out a way to count the distance(s) between the winning number and the nearest number (in regards to the winning number) of the combination that I get from the predictor.

To better explain my question I have added a short video that shows my issue.

NOTE: So, in the video you see that the "winning number = 9", and the predictor creates three combinations to bet on.
In this case combo A gives 21 14 26 / combo B gives 24 17 29 / combo C gives 22 17 29:

1. Because in "combo A" the nearest number to "9" is 14, which is two pockets away (Distance = 2). So the result must be 2 in this case.
2. In "combo B" the nearest number to "9" is 29, which is three pockets away (Distance = 3). So the result must be 3 in this case.
3. In "combo C" the nearest number to "9" is 22, which is one pocket away (Distance = 1). So the result must be 1 in this case.

VEED - Distance roulette

Make stunning videos with a single click. Cut, trim, crop, add subtitles and more. Online, no account needed. Try it now, free. VEED

www.veed.io

I have no idea if this is possible. Any advice would be appreciated.


Thank you!

 

[StephenCrump]

Are your regional settings the same as mine?

For example, I say: MATCH(F2,Wheel,)

Would you perhaps say: MATCH(F2;Wheel; )?

 

[JanusValeri]

Are your regional settings the same as mine?

For example, I say: MATCH(F2,Wheel,)

Would you perhaps say: MATCH(F2;Wheel; )?

Oh yes, that could be it. I'm situated in Europe (Germany). All my code is with ";".
But still it didn't work. I changed all "," to ";" to test.

Should I add something after "Wheel; ...)"?

 

[JanusValeri]

Hey StephenCrump, I tried it again (adjusted the code to my regional settings) and now it works like a charm.

Thanks for all the help!

 

[JanusValeri]

Are your regional settings the same as mine?

For example, I say: MATCH(F2,Wheel,)

Would you perhaps say: MATCH(F2;Wheel; )?

I do have one final question: If I happen to get a combo like "24 17 /". So only the two first cells contain numbers in this example.

How would I change the formula so it ignores the "/" and let the formula only look at the first two numbers in this case. So that I wouldn't get "#N/A" as a result?

 

[StephenCrump]

Try:

	A	B	C	D	E	F	G
1		Picks				Result	Distance
2		A	18		23	9	2
3		B	21		AAA	9	15
4		C				9	n/a
5		D		29		9	3
6		E	/	/	/	9	n/a
7		F	/	19	/	9	13
8
9			Wheel
10			0
11			26
12			3
13			35
14			12
15			28
16			7
17			29
18			18
19			22
20			9
21			31
22			14
23			20
24			1
25			33
26			16
27			24
28			5
29			10
30			23
31			8
32			30
33			11
34			36
35			13
36			27
37			6
38			34
39			17
40			25
41			2
42			21
43			4
44			19
45			15
46			32
47
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IFERROR(AGGREGATE(15,6,ABS(MATCH(IF(LEN(C2:E2),C2:E2),wheel,)-MATCH(F2,wheel,)+{-37;0;37}),1),"n/a")

Named Ranges
Name	Refers To	Cells
wheel	=Sheet3!$C$10:$C$46	G2:G7

 

[Legacy 143009]

Here is a working version of my code. I added few veriables to enable you to customize the code according to your needs:

Sub nearestDistance()
  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances() As Variant, smallest() As Integer, startRow As Integer, startColumn As Integer, numberOfColumns As Integer, numberOfRows As Integer, resultsColumn As Integer
  Dim found As Boolean, isNumber As Boolean
  startRow = 42
  startColumn = 3
  numberOfRows = 6
  numberOfColumns = 5
  resultsColumn = 11
  ReDim distances(numberOfRows, numberOfColumns)
  ReDim smallest(numberOfRows)
  rNumbers = Split("0,26,3,35,12,28,7,29,18,22,9,31,14,20,1,33,16,24,5,10,23,8,30,11,36,13,27,6,34,17,25,2,21,4,19,15,32", ",")
 
  For r = startRow To startRow + numberOfColumns - 1
    For k = startColumn To startColumn + numberOfColumns - 1
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
         If IsNumeric(Cells(r, k).Value) Then
          For i = j To UBound(rNumbers)
              isNumber = True
              If CInt(rNumbers(i)) = Cells(r, k).Value Then
                found = True
              Exit For
            End If
            counter1 = counter1 + 1
          Next i
          If Not found Then
            For i = j To LBound(rNumbers) Step -1
              If CInt(rNumbers(i)) = Cells(r, k).Value Then
                Exit For
              End If
              counter2 = counter2 + 1
            Next i
          End If
            Else
            isNumber = False
            End If
          If isNumber Then
          distances(r - startRow, k - startColumn) = IIf(found, counter1, counter2)
          Else
          distances(r - startRow, k - startColumn) = ""
          End If
          Exit For
        End If
      Next j
    Next k
    If Not IsEmpty(distances(r - startRow, 0)) Then
      smallest(r - startRow) = distances(r - startRow, 0)
    End If
    For s = 1 To numberOfColumns - 1
      If Not IsEmpty(distances(r - startRow, s)) And distances(r - startRow, s) < smallest(r - startRow) Then
        smallest(r - startRow) = distances(r - startRow, s)
      End If
    Next s
    Cells(r, resultsColumn).Value = smallest(r - startRow)
  Next r
End Sub

 

[JanusValeri]

Try:

	A	B	C	D	E	F	G
1		Picks				Result	Distance
2		A	18		23	9	2
3		B	21		AAA	9	15
4		C				9	n/a
5		D		29		9	3
6		E	/	/	/	9	n/a
7		F	/	19	/	9	13
8
9			Wheel
10			0
11			26
12			3
13			35
14			12
15			28
16			7
17			29
18			18
19			22
20			9
21			31
22			14
23			20
24			1
25			33
26			16
27			24
28			5
29			10
30			23
31			8
32			30
33			11
34			36
35			13
36			27
37			6
38			34
39			17
40			25
41			2
42			21
43			4
44			19
45			15
46			32
47
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IFERROR(AGGREGATE(15,6,ABS(MATCH(IF(LEN(C2:E2),C2:E2),wheel,)-MATCH(F2,wheel,)+{-37;0;37}),1),"n/a")

Named Ranges
Name	Refers To	Cells
wheel	=Sheet3!$C$10:$C$46	G2:G7

Hey StephenCrump, this adjustment works great! Thank you for this

 

[JanusValeri]

Here is a working version of my code. I added few veriables to enable you to customize the code according to your needs:

Sub nearestDistance()
  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances() As Variant, smallest() As Integer, startRow As Integer, startColumn As Integer, numberOfColumns As Integer, numberOfRows As Integer, resultsColumn As Integer
  Dim found As Boolean, isNumber As Boolean
  startRow = 42
  startColumn = 3
  numberOfRows = 6
  numberOfColumns = 5
  resultsColumn = 11
  ReDim distances(numberOfRows, numberOfColumns)
  ReDim smallest(numberOfRows)
  rNumbers = Split("0,26,3,35,12,28,7,29,18,22,9,31,14,20,1,33,16,24,5,10,23,8,30,11,36,13,27,6,34,17,25,2,21,4,19,15,32", ",")
 
  For r = startRow To startRow + numberOfColumns - 1
    For k = startColumn To startColumn + numberOfColumns - 1
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
         If IsNumeric(Cells(r, k).Value) Then
          For i = j To UBound(rNumbers)
              isNumber = True
              If CInt(rNumbers(i)) = Cells(r, k).Value Then
                found = True
              Exit For
            End If
            counter1 = counter1 + 1
          Next i
          If Not found Then
            For i = j To LBound(rNumbers) Step -1
              If CInt(rNumbers(i)) = Cells(r, k).Value Then
                Exit For
              End If
              counter2 = counter2 + 1
            Next i
          End If
            Else
            isNumber = False
            End If
          If isNumber Then
          distances(r - startRow, k - startColumn) = IIf(found, counter1, counter2)
          Else
          distances(r - startRow, k - startColumn) = ""
          End If
          Exit For
        End If
      Next j
    Next k
    If Not IsEmpty(distances(r - startRow, 0)) Then
      smallest(r - startRow) = distances(r - startRow, 0)
    End If
    For s = 1 To numberOfColumns - 1
      If Not IsEmpty(distances(r - startRow, s)) And distances(r - startRow, s) < smallest(r - startRow) Then
        smallest(r - startRow) = distances(r - startRow, s)
      End If
    Next s
    Cells(r, resultsColumn).Value = smallest(r - startRow)
  Next r
End Sub

View attachment 80858

Thank you Flashbond! I tried your code as well and oh my! It works great! Thanks a lot for all your time and effort