# Find 3rd instance of a value in a column

#### ThomasOES

##### Board Regular
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")
Code:
``[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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try this. Change the data in red for your data

Code:
``````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
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``````

Replies
3
Views
39
Replies
21
Views
2K
Replies
8
Views
773

1,203,240
Messages
6,054,319
Members
444,717
Latest member
melindanegron

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back