VBA Code Help

[ststern45]

Below is the code for calculating "how far back" or in other words how many previous drawings did it take for all 3 digits to appear from each of the 3 positions.
The code below is for a 3 digits that range from 0 through 9. Digits can repeat from the original set of 3 numbers analyzed.
3 digit/position example:
Cell range S20 through U20
S20 = 4
T20 = 2
U20 = 1
Starting from cell range S20 through U19 the calculations look at the following digits:
The result would be 7
4 2 1 <Cell S20, T20, U20 For all 3 digits it required 7 previous rows for all 3 digits to appear
1 6 3 <1
0 2 3 <2
6 7 8 <3
7 3 5 <4
1 2 1 <5
3 3 5 <6
4 4 7 <7
6 0 2
7 6 0
4 1 0
7 7 5
7 4 1
4 4 0
0 8 5
7 3 6
1 0 5
5 2 6
9 8 0
8 9 6
1 2 7
0 9 1
9 3 7
1 5 2
9 9 0
8 6 6
1 0 8
8 4 3
1 4 9
3 2 3
3 5 4
0 4 1
7 8 3
9 6 1
6 1 1
5 3 9
5 2 6
4 2 1
6 6 1
6 5 8
9 2 3
3 5 7
0 8 5
5 3 8
0 4 3
1 9 9
2 6 6
4 0 1
5 0 2
0 4 4
4 9 7
7 2 5
9 5 9
1 4 5
4 4 3
8 6 6
7 8 1
7 2 6
4 8 9
9 4 6
5 4 1
8 5 1
8 0 1
0 4 1
2 0 6
0 5 8
5 2 0
3 5 1
1 0 2
6 9 7
8 1 5
6 5 4
0 1 9
5 9 1
5 4 4
6 0 8
7 2 3
0 1 5
3 5 0
9 6 2
2 2 7
0 3 9
6 3 7
9 7 0
4 6 4
9 7 9
6 5 0
9 5 1
6 9 9
6 2 4
8 6 0
7 1 3
6 8 7
2 9 1
8 4 0
4 0 5
4 7 4
4 1 5
0 6 9
7 1 1

Public Function HowFar(ByVal MyRange As Range, ByVal MyCount As Long)
Dim MyData As Variant, i As Long, j As Long, k As Long

    MyData = MyRange.Value
    For i = 2 To UBound(MyData)
        For j = 1 To 3
            For k = 1 To 3
                If MyData(i, j) = MyData(1, k) Then
                    MyCount = MyCount - 1
                    MyData(1, k) = "x"
                    If MyCount = 0 Then
                        HowFar = i - 1
                        Exit Function
                    End If
                    Exit For
                End If
            Next k
        Next j
    Next i
   
    HowFar = ""
End Function

I need help with the code in order to analyze 4 digits instead of 3 digits.

Thank you in advance!!

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

From what I can deduce, the function does not need the argument MyCount. Isn't MyCount automatically just the number of columns in MyRange?
If I am right then what about this function?

Public Function HowFar(ByVal MyRange As Range) As Variant
  Dim MyData As Variant, i As Long, j As Long, k As Long, cols As Long, MyCount As Long

    MyData = MyRange.Value
    MyCount = MyRange.Columns.Count
    cols = MyCount
    For i = 2 To UBound(MyData)
        For j = 1 To cols
            For k = 1 To cols
                If MyData(i, j) = MyData(1, k) Then
                    MyCount = MyCount - 1
                    MyData(1, k) = "x"
                    If MyCount = 0 Then
                        HowFar = i - 1
                        Exit Function
                    End If
                    Exit For
                End If
            Next k
        Next j
    Next i
  
    HowFar = ""
End Function

A couple of examples:

ststern45.xlsm
	S	T	U	V	W	X
19
20	4	2	1			7
21	1	6	3
22	0	2	3
23	6	7	8
24	7	3	5
25	1	2	1
26	3	3	5
27	4	4	7
28	6	0	2
29	7	6	0
30
31
32
33	4	2	9	1		4
34	1	6	5	3
35	0	2	5	3
36	6	0	2	2
37	4	7	9	8
38	7	3	2	5
39	1	2	2	1
40	3	3	2	5
41	4	4	2	7
42	6	0	2	2
43	7	6	9	0
44	4	1	6	0
45	7	7	4	5
46	7	4	6	1
47
Sheet1

Cell Formulas
Range	Formula
X20	X20	=Howfar(S20:U29)
X33	X33	=Howfar(S33:V46)

 

[Peter_SSs]

I don't know if this would be of interest to you, but here is another option with a lot less looping.

Function Dist(MyRange As Range) As Variant
  Dim rFound As Range, c As Range
  Dim MaxRow As Long
  
  Dist = ""
  For Each c In MyRange.Rows(1).Cells
    Set rFound = MyRange.Find(What:=c.Value, After:=MyRange.Cells(1, MyRange.Columns.Count), LookAt:=xlWhole, SearchOrder:=xlByRows)
    If rFound.Row = MyRange.Row Then
      Exit Function
    Else
      If rFound.Row > MaxRow Then MaxRow = rFound.Row
    End If
  Next c
  Dist = MaxRow - MyRange.Row
End Function

ststern45.xlsm
	S	T	U	V	W		Y
20	4	2	1				7
21	1	6	3
22	0	2	3
23	6	7	8
24	7	3	5
25	1	2	1
26	3	3	5
27	4	4	7
28	6	0	2
29	7	6	0
30
31
32
33	4	2	9	1			4
34	1	6	5	3
35	0	2	5	3
36	6	0	2	2
37	4	7	9	8
38	7	3	2	5
39	1	2	2	1
40	3	3	2	5
41	4	4	2	7
42	6	0	2	2
43	7	6	9	0
44	4	1	6	0
45	7	7	4	5
46	7	4	6	1
47
Sheet1

Cell Formulas
Range	Formula
Y20	Y20	=Dist(S20:U29)
Y33	Y33	=Dist(S33:V46)

 

[ststern45]

Thank you Peter.

 

[Peter_SSs]

You're welcome. Glad to help.