Lookup second duplicate value location.
 

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) ?

Lookup second duplicate value location.
 

I like using a worksheet function to do this


A1 A 1 =countif(A$1:A1,A1)
A2 B 1 =countif(A$1:A2,A2)
A3 C 1 =countif(A$1:A3,A3)
A4 D 1 =countif(A$1:A4,A4)
A5 E 1 =countif(A$1:A5,A5)
A6 C 2 =countif(A$1:A6,A6)
A7 B 2 =countif(A$1:A7,A7)
A8 F 1 =countif(A$1:A8,A8)


LastRow = Range("A" & rows.count).end(xlup).Row
Range("B1").formual = _
"=countif(A$1:A1,A1)"
Range("B1").copy _
Destination:=Range("B1:B" & LastRow)


the results in column B greater then 1 is the duplicate results. Yo
can then use an autofilter to select values 1 to get the results.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147822

Lookup second duplicate value location.
 

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

"Marina" wrote:

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) ?
.