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)

[TABLE="width: 266"]
<tbody>[TR]
[TD]Name[/TD]
[TD]CS 844[/TD]
[TD]Drift Correct[/TD]
[/TR]
[TR]
[TD]501-676 0684[/TD]
[TD]0.134[/TD]
[TD]0.131[/TD]
[/TR]
[TR]
[TD]NIST 293[/TD]
[TD]0.216[/TD]
[TD]0.211[/TD]
[/TR]
[TR]
[TD]4Q18L1[/TD]
[TD]0.205[/TD]
[TD]0.201[/TD]
[/TR]
[TR]
[TD]BS CSN 2-2[/TD]
[TD]0.547[/TD]
[TD]0.535[/TD]
[/TR]
[TR]
[TD]4Q18L2[/TD]
[TD]0.197[/TD]
[TD]0.193[/TD]
[/TR]
[TR]
[TD]NIST 291[/TD]
[TD]0.17[/TD]
[TD]0.167[/TD]
[/TR]
[TR]
[TD]501-676 0684[/TD]
[TD]0.142[/TD]
[TD]0.139[/TD]
[/TR]
[TR]
[TD]NIST 293[/TD]
[TD]0.22[/TD]
[TD]0.216[/TD]
[/TR]
[TR]
[TD]4Q18L1[/TD]
[TD]0.207[/TD]
[TD]0.203[/TD]
[/TR]
[TR]
[TD]BS CSN 2-2[/TD]
[TD]0.54[/TD]
[TD]0.53[/TD]
[/TR]
[TR]
[TD]4Q18L2[/TD]
[TD]0.205[/TD]
[TD]0.201[/TD]
[/TR]
[TR]
[TD]NIST 291[/TD]
[TD]0.175[/TD]
[TD]0.172[/TD]
[/TR]
[TR]
[TD]501-676 0684[/TD]
[TD]0.135[/TD]
[TD]0.133[/TD]
[/TR]
[TR]
[TD]NIST 293[/TD]
[TD]0.221[/TD]
[TD]0.217[/TD]
[/TR]
[TR]
[TD]4Q18L1[/TD]
[TD]0.204[/TD]
[TD]0.201[/TD]
[/TR]
[TR]
[TD]BS CSN 2-2[/TD]
[TD]0.547[/TD]
[TD]0.539[/TD]
[/TR]
[TR]
[TD]4Q18L2[/TD]
[TD]0.206[/TD]
[TD]0.203[/TD]
[/TR]
[TR]
[TD]NIST 291[/TD]
[TD]0.147[/TD]
[TD]0.145[/TD]
[/TR]
[TR]
[TD]501-676 0684[/TD]
[TD]0.135[/TD]
[TD]0.133[/TD]
[/TR]
</tbody>[/TABLE]

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