Lookup second duplicate value location.

[Marina]

How to write VBA code to find the row location of a second duplicate
value in a list( ie a second "apple" in a list of fruits name) ?

 

[Mike H]

Marina,

You don't need VB for this it can be done with a formula but if you want code

Sub sonic()
Dim Found As Long, x As Long, LastRow As Long
Dim ResPonse As String
ResPonse = Trim(InputBox("Enter value to search for"))
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = LastRow To 1 Step -1
If UCase(Trim(Cells(x, 1).Value)) = UCase(ResPonse) Then
Found = Found + 1
If Found = 2 Then
MsgBox "second occurrence of " & ResPonse & " in row " & x
Exit Sub
End If
End If
Next
MsgBox "Search string not found"
End Sub

Mike