Count the number of skips between hits

[PokerJoe]

Cash5v1.1.xls
	A	B	C	D	E	F	G	H	I	J	K
1		C1	C2	C3	C4	C5	SBH1	SBH2	SBH3	SBH4	SBH5
2	H1	9h	10s	Qh	Jd	5h
3	H2	2d	7s	7d	Ac	4s
4	H3	4d	Kd	Ks	Jc	2h
5	H4	5c	5h	2d	8s	8c
6	H5	10d	Jd	Qd	Ks	4c
7	H6	7h	Qs	Qh	As	10c
8	H7	10s	2d	5d	Ah	Ac
9	H8	3c	6h	6c	Ad	5c
10	H9	2h	9h	10s	Ks	Jc
11	H10	Jd	5h	2d	8s	10c
12	H11	7s	7h	Qd	2d	10d
13	H12	8s	2d	2h	9h	10s
14	H13	Ks	Kd	6c	Qh	4s
15	H14	As	10s	7s	3c	6h
16	H15	2h	Ah	Jd	Qd	Ac
Sheet2

Private Sub Count_Skips_Between_Hits()
LastRow = Cells(65536, 1).End(xlUp).Row
    For I = 2 To LastRow
            Cells(I, 7).FormulaR1C1 = 'Count the number of skips since the last time this value occurred in any column"
            Cells(I, 8).FormulaR1C1 = "='Count the number of skips since the last time this value occurred in any column"
            Cells(I, 9).FormulaR1C1 = 'Count the number of skips since the last time this value occurred in any column"
            Cells(I, 10).FormulaR1C1 = 'Count the number of skips since the last time this value occurred in any column"
            Cells(I, 11).FormulaR1C1 = "='Count the number of skips since the last time this value occurred in any column"
    Next I
Columns("G:K").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Is there a way to count the number of skips between two ocurrences?

In other words I need to know how many hands were played between the time any card was drawn?

Using the example, "I7" should = 4 and "J12" should = 0.

Any takers to point me in the right direction?

 

[Ahnold]

This won't put formulas in the target cells, but it will fill in the values you are seeking. See if this works for you.

Private Sub Count_Skips_Between_Hits()
    Dim myRange As Range
    Dim c As Range
    Set myRange = Range("B2", Range("F65536").End(xlUp))
    
    For Each c In myRange
        For i = c.Row - 2 To 1 Step -1
            If Not IsError(Application.Match(c, myRange.Rows(i), 0)) Then
                c.Offset(, 5) = c.Row - 2 - i
                Exit For
            Else
                c.Offset(, 5) = 0
            End If
        Next i
    Next c
End Sub

 

[PokerJoe]

Amazing...THANK YOU

You guys just continue to amaze me. I'll never be the level of genius many of you are. I'm grateful for your generosity.

ray: ray: ray: THANK YOU AHNOLD!!!

It was exactly what I needed. YOU DA MAN!!!!