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



## JanusValeri (Dec 9, 2022)

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:

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


----------



## Flashbond (Dec 10, 2022)

Can you provide the cell references for
- Where you put number 9
- Were are combo A, B, C cells?
- Where the results to be displayed?


----------



## JanusValeri (Dec 10, 2022)

Hey Flashbond,

Number 9 is entered in cell Q50
In this example "combo A" is located in cells C45, D45 and E45; "combo B" is located in cells C46, D46 and E46; "combo C" is located in cells C47, D47 and E47
The results can be displayed in cell K45 for combo A; K46 for combo B; K47 for combo C. 
Thanks!


----------



## Flashbond (Dec 11, 2022)

Could you test it please?

```
Sub nearestDistance()

  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances(3, 3) As Integer, smallest(3) As Integer
  Dim found As Boolean

  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 = 45 To 47
    For k = 3 To 5
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
          For i = j + 1 To UBound(rNumbers)
            counter1 = counter1 + 1
            If CInt(rNumbers(i)) = Cells(r, k).Value Then
              found = True
              Exit For
            End If
          Next
          If Not found Then
            For i = j - 1 To LBound(rNumbers) Step -1
              counter2 = counter2 + 1
              If CInt(rNumbers(i)) = Cells(r, k).Value Then
                Exit For
              End If
            Next
          End If
        distances(r - 45, k - 3) = IIf(found, counter1, counter2)
        End If
      Next
    Next
 
    For x = 0 To 1
      For y = x + 1 To 2
        If distances(r - 45, x) < distances(r - 45, y) Then
          smallest(r - 45) = distances(r - 45, x)
        Else
          smallest(r - 45) = distances(r - 45, y)
        End If
      Next
    Next

    Cells(r, 11).Value = smallest(r - 45)
  Next
End Sub
```


----------



## JanusValeri (Dec 11, 2022)

Hey Flashbond, Thanks again for your wonderful help!

There's only an issue in row 47: in column K it gives "2" as an answer, but looking at the table number 22 is nearest to 9 (so the correct answer would need to return "1" as an answer) and now it thinks number 18 is nearest to 9.

I have two further questions if you don't mind:

If I would get a combo like "21, 14, 26, 5, 9", can the code *return the answer "0"* (as 9 itself is the closest number to the "winning number 9")?
Can you adjust the code to 6 combo's: so in the possibility that I would get 6 combo's instead of 3. In short, that I get *combo's to show in rows 42 to 47* instead of from  rows 45 to 47?

Thanks in advance!


----------



## Flashbond (Dec 11, 2022)

How about this one:

```
Sub nearestDistance()
  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances(6, 3) As Integer, smallest(6) As Integer
  Dim found As Boolean

  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 = 42 To 47
    For k = 3 To 5
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
          For i = j To UBound(rNumbers)
            If CInt(rNumbers(i)) = Cells(r, k).Value Then
              found = True
              Exit For
            End If
            counter1 = counter1 + 1
          Next
          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
          End If
        distances(r - 42, k - 3) = IIf(found, counter1, counter2)
        End If
      Next
    Next
    smallest(r - 42) = distances(r - 42, 0)
    For s = 1 To 2
      If distances(r - 42, s) < smallest(r - 42) Then
        smallest(r - 42) = distances(r - 42, s)
      End If
    Next
    Cells(r, 11).Value = smallest(r - 42)
  Next
End Sub
```


----------



## JanusValeri (Dec 11, 2022)

Wow this works perfect! Thanks for your amazing help.

One thing: Do you know why the code doesn't happen automatically?
Now after every new number I enter in "Q50" I need to right-click the sheet and go to "View Code" and hit the "Run Sub" button. I have placed this sub right after the previous code I got from you for counting unchanged cells. Maybe this has something to do with it?

You can see the two subs below each other in the attached image.


----------



## Flashbond (Dec 11, 2022)

You can add the following into the previous Change Event:

```
If Not Intersect(Target, Range("Q50")) Is Nothing Then 'Or the cell address you change in order to modify Q50
  Application.EnableEvents = False
  Call nearestDistance
  Application.EnableEvents = True
End If
```


----------



## JanusValeri (Dec 12, 2022)

l believe to have done something wrong with adjusting the code. So I entered your code above, but now both the "Change" and "nearestDistance" event don't update in their cells.
Maybe it has something to do with cell "Q50". Because I want that particular cell to be the one that activates both codes.

Here's the code that I now have:


```
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Q50")) Is Nothing Then 'Or the cell address you change in order to modify Q50
  Application.EnableEvents = False
  Call nearestDistance
  For i = 42 To 47
    If Cells(i, 9).Value = 1 Then
      Cells(i, 10).Value = Cells(i, 10).Value + 1
    Else
      Cells(i, 10).Value = 0
    End If
  Next
  Application.EnableEvents = True
  End If
End Sub

Sub nearestDistance()
  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances(6, 3) As Integer, smallest(6) As Integer
  Dim found As Boolean

  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 = 42 To 47
    For k = 3 To 5
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
          For i = j To UBound(rNumbers)
            If CInt(rNumbers(i)) = Cells(r, k).Value Then
              found = True
              Exit For
            End If
            counter1 = counter1 + 1
          Next
          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
          End If
        distances(r - 42, k - 3) = IIf(found, counter1, counter2)
        End If
      Next
    Next
    smallest(r - 42) = distances(r - 42, 0)
    For s = 1 To 2
      If distances(r - 42, s) < smallest(r - 42) Then
        smallest(r - 42) = distances(r - 42, s)
      End If
    Next
    Cells(r, 11).Value = smallest(r - 42)
  Next
End Sub
```


----------



## Flashbond (Dec 12, 2022)

Interesting.. The code seems to be OK. Maybe Application.EnableEvents left False.
First run the code below once and then try to change Q50 again:

```
Sub test()
  Application.EnableEvents = True
End Sub
```
Also, the code must be in the same sheet with the data.


----------



## JanusValeri (Dec 9, 2022)

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:

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


----------



## JanusValeri (Dec 12, 2022)

Works like a charm! 😊
Final question, if I want to adjust a small detail to the code: What if in the future I get a combo of five numbers "12 30 10 32 22". And the number in Q50 is "9". 
The nearest number in this case is the fifth one "22". Where to adjust the code so I would get "1" as the answer?

Big thanks!


----------



## Flashbond (Dec 12, 2022)

In the code r stands for row and k stands for column. The code should look like this. It will search through column C to G. I am posting without testing. Let me know if this works. If not, I can look at later.

```
Sub nearestDistance()
  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances(6, 5) As Integer, smallest(6) As Integer
  Dim found As Boolean

  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 = 42 To 47
    For k = 3 To 7
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
          For i = j To UBound(rNumbers)
            If CInt(rNumbers(i)) = Cells(r, k).Value Then
              found = True
              Exit For
            End If
            counter1 = counter1 + 1
          Next
          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
          End If
        distances(r - 42, k - 3) = IIf(found, counter1, counter2)
        Exit For
        End If
      Next
    Next
    smallest(r - 42) = distances(r - 42, 0)
    For s = 1 To 4
      If distances(r - 42, s) < smallest(r - 42) Then
        smallest(r - 42) = distances(r - 42, s)
      End If
    Next
    Cells(r, 11).Value = smallest(r - 42)
  Next
End Sub
```


----------



## JanusValeri (Dec 12, 2022)

It worked perfect. Thanks for all the help Flashbond!


----------



## Flashbond (Dec 12, 2022)

Bonne chance


----------



## JanusValeri (Dec 12, 2022)

Hey Flashbond, for some reason I keep getting an error message every time I test a new number.
I haven't changed anything to the code. The error stays the same: *"Run-time error '13': Type mismatch"*.

Any idea why this happens?


```
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Q50")) Is Nothing Then 'Or the cell address you change in order to modify Q50
  Application.EnableEvents = False
  Call nearestDistance
  For i = 42 To 47
    If Cells(i, 9).Value = 1 Then
      Cells(i, 10).Value = Cells(i, 10).Value + 1
    Else
      Cells(i, 10).Value = 0
    End If
  Next
  Application.EnableEvents = True
  End If
End Sub

Sub nearestDistance()
  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances(6, 5) As Integer, smallest(6) As Integer
  Dim found As Boolean

  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 = 42 To 47
    For k = 3 To 7
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
          For i = j To UBound(rNumbers)
            If CInt(rNumbers(i)) = Cells(r, k).Value Then
              found = True
              Exit For
            End If
            counter1 = counter1 + 1
          Next
          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
          End If
        distances(r - 42, k - 3) = IIf(found, counter1, counter2)
        End If
      Next
    Next
    smallest(r - 42) = distances(r - 42, 0)
    For s = 1 To 4
      If distances(r - 42, s) < smallest(r - 42) Then
        smallest(r - 42) = distances(r - 42, s)
      End If
    Next
    Cells(r, 11).Value = smallest(r - 42)
  Next
End Sub
```

I have only 1 sheet btw.


----------



## Flashbond (Dec 12, 2022)

That's because you have non numeric values. The workaround is difficult. Try something like this. I can not guarantee it will work:

```
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Q50")) Is Nothing Then 'Or the cell address you change in order to modify Q50
  Application.EnableEvents = False
  Call nearestDistance
  For i = 42 To 47
    If Cells(i, 9).Value = 1 Then
      Cells(i, 10).Value = Cells(i, 10).Value + 1
    Else
      Cells(i, 10).Value = 0
    End If
  Next
  Application.EnableEvents = True
  End If
End Sub

Sub nearestDistance()
  Dim rNumbers As Variant
  Dim counter1 As Integer, counter2 As Integer, distances(6, 5) As Integer, smallest(6) As Integer
  Dim found As Boolean, isNumber Boolean

  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 = 42 To 47
    For k = 3 To 7
      counter1 = 0
      counter2 = 0
      For j = 0 To UBound(rNumbers)
        found = False
        If CInt(rNumbers(j)) = Cells(50, 17).Value Then
          For i = j To UBound(rNumbers)
            If IsNumeric(Cells(r, k).Value) Then
              isNumber = True
              If CInt(rNumbers(i)) = Cells(r, k).Value Then
              found = True
              Exit For
            End If
            counter1 = counter1 + 1
          Next
          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
          End If
            Else
            isNumber = False
            End If
          If isNumber Then
          distances(r - 42, k - 3) = IIf(found, counter1, counter2)
          End If
          Exit For
        End If
      Next
    Next
    If Not IsEmpty(distances(r - 42, 0)) Then
      smallest(r - 42) = distances(r - 42, 0)
    End If
    For s = 1 To 4
      If Not IsEmpty(distances(r - 42, s)) And distances(r - 42, s) < smallest(r - 42) Then
        smallest(r - 42) = distances(r - 42, s)
      End If
    Next
    Cells(r, 11).Value = smallest(r - 42)
  Next
End Sub
```


----------



## JanusValeri (Dec 12, 2022)

Flashbond said:


> That's because you have non numeric values. The workaround is difficult. Try something like this. I can not guarantee it will work:
> 
> ```
> Private Sub Worksheet_Change(ByVal Target As Range)
> ...


Thanks, I will try it out!


----------



## JanusValeri (Dec 12, 2022)

Flashbond said:


> That's because you have non numeric values. The workaround is difficult. Try something like this. I can not guarantee it will work:
> 
> ```
> Private Sub Worksheet_Change(ByVal Target As Range)
> ...


Unfortunately, it doesn't work. Is there an alternative solution to this kind of issue?


----------



## StephenCrump (Dec 12, 2022)

I know you asked for VBA code, but you could also use a fairly simple formula:

ABCDEFG1PicksResultDistance2A181123923B211426924C1963985D211426926E241729937F22919089Wheel10011261231335141215281671729181819222092131221423202412533261627242852910302331832303311343635133627376383439174025412422143444194515463247Sheet1Cell FormulasRangeFormulaG2:G7G2=MIN(ABS(MATCH(C2:E2,Wheel,)-MATCH(F2,Wheel,)+{-37;0;37}))Press CTRL+SHIFT+ENTER to enter array formulas.Named RangesNameRefers ToCellsWheel=Sheet1!$C$10:$C$46G2:G7


----------



## JanusValeri (Dec 13, 2022)

StephenCrump said:


> I know you asked for VBA code, but you could also use a fairly simple formula:
> 
> ABCDEFG1PicksResultDistance2A181123923B211426924C1963985D211426926E241729937F22919089Wheel10011261231335141215281671729181819222092131221423202412533261627242852910302331832303311343635133627376383439174025412422143444194515463247Sheet1Cell FormulasRangeFormulaG2:G7G2=MIN(ABS(MATCH(C2:E2,Wheel,)-MATCH(F2,Wheel,)+{-37;0;37}))Press CTRL+SHIFT+ENTER to enter array formulas.Named RangesNameRefers ToCellsWheel=Sheet1!$C$10:$C$46G2:G7


Hey StephenCrump, thank you for your formula! Indeed, it doesn't have to be a VBA code.

But for some reason I can't paste the formula in my Excel sheet. It keeps giving me an error message. Does it have to do with me working on Mac instead of Windows?
I attached an image of the error message so you can see what I enter in the cells.

Thanks in advance.


----------



## JanusValeri (Dec 9, 2022)

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:

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.
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.
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 (Dec 13, 2022)

Are your regional settings the same as mine?  

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

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


----------



## JanusValeri (Dec 13, 2022)

StephenCrump said:


> 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 (Dec 13, 2022)

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 (Dec 13, 2022)

StephenCrump said:


> 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 (Dec 13, 2022)

Try:

ABCDEFG1PicksResultDistance2A1823923B21AAA9154C9n/a5D29936E///9n/a7F/19/91389Wheel10011261231335 141215281671729181819222092131221423202412533261627242852910302331832303311343635133627376383439174025412422143444194515463247Sheet1Cell FormulasRangeFormulaG2:G7G2=IFERROR(AGGREGATE(15,6,ABS(MATCH(IF(LEN(C2:E2),C2:E2),wheel,)-MATCH(F2,wheel,)+{-37;0;37}),1),"n/a")Named RangesNameRefers ToCellswheel=Sheet3!$C$10:$C$46G2:G7


----------



## Flashbond (Dec 14, 2022)

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 (Dec 14, 2022)

StephenCrump said:


> Try:
> 
> ABCDEFG1PicksResultDistance2A1823923B21AAA9154C9n/a5D29936E///9n/a7F/19/91389Wheel10011261231335 141215281671729181819222092131221423202412533261627242852910302331832303311343635133627376383439174025412422143444194515463247Sheet1Cell FormulasRangeFormulaG2:G7G2=IFERROR(AGGREGATE(15,6,ABS(MATCH(IF(LEN(C2:E2),C2:E2),wheel,)-MATCH(F2,wheel,)+{-37;0;37}),1),"n/a")Named RangesNameRefers ToCellswheel=Sheet3!$C$10:$C$46G2:G7


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


----------



## JanusValeri (Dec 14, 2022)

Flashbond said:


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


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


----------

