Find 3rd instance of a value in a column

[ThomasOES]

Hello
In a column with multiple names that has some names repeating, I would like to find the third instance of a particular name and then refer to a value two columns over.

For instance if below Name is column A, CS844 is column B, Drift Correct is column C. Lets suppose Rows 1-20.
Currently to find the drift correct value for the first instance of "NIST 293" I use my code below
"NIST 293" appears on Sheets("Report").Range("B6")

[FONT=Verdana]Range("A1:A20").Find(Sheets("Report").Range("B6")).Offset(,2).Select[/FONT]

Of course this will select the first instance in the column (0.211).
What if I want to select the third instance? (0.217)

Name	CS 844	Drift Correct
501-676 0684	0.134	0.131
NIST 293	0.216	0.211
4Q18L1	0.205	0.201
BS CSN 2-2	0.547	0.535
4Q18L2	0.197	0.193
NIST 291	0.17	0.167
501-676 0684	0.142	0.139
NIST 293	0.22	0.216
4Q18L1	0.207	0.203
BS CSN 2-2	0.54	0.53
4Q18L2	0.205	0.201
NIST 291	0.175	0.172
501-676 0684	0.135	0.133
NIST 293	0.221	0.217
4Q18L1	0.204	0.201
BS CSN 2-2	0.547	0.539
4Q18L2	0.206	0.203
NIST 291	0.147	0.145
501-676 0684	0.135	0.133

<tbody>
</tbody>

Thanks

Tom

 

[DanteAmor]

Try this. Change the data in red for your data

Sub Find_Instance()


    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim Instance As Double, n As Double
    Dim r As Range, b As Range
    Dim celda As String, existe As Boolean
    
    Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Report[/COLOR]")
    Instance = [COLOR=#ff0000]3[/COLOR]
    
    sh1.Select
    Set r = sh1.Columns("A")
    Set b = r.Find(sh2.Range("B6").Value, LookAt:=xlWhole, LookIn:=xlValues)
    
    If Not b Is Nothing Then
        celda = b.Address
        n = 1
        Do
            'detalle
            If n = Instance Then
                b.Offset(0, 2).Select
                existe = True
                MsgBox "Found"
                Exit Do
            Else
                n = n + 1
            End If
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> celda
        If existe = False Then
            MsgBox "There is no instance number : " & Instance
        End If
    Else
        MsgBox "There is no data : " & sh2.Range("B6").Value
    End If
End Sub