Identify cell address of first instance of duplicate value in vba

[dfmaniquiz]

I have a table
Column A, Column B, Column C
Row1 2 Red Apple
Row2 5 Green Mango
Row3 2 Yellow Banana

Selected row3 with duplicate in Column A = value 2. I need a code or function to find the first instance of duplicate value 2 in column A then get the address of the cell in column C which is the address of Apple or get the cell value Apple. Thank you so much.

 

[mumps]

When you get cell value "Apple", what do you want to do with it?

 

[dfmaniquiz]

The table is representation of larger database where i created a form to display the details of an entire row. Column C is a unique ID which was previously used by user represented by Column A and in this case user 2. The entry Apple will be displayed in a userform inputbox when the userform is displaying the details of the 3rd row.

 

[dfmaniquiz]

In any given time there will only be 2 duplicate entries allowed.

 

[mumps]

See if this helps:

Sub FindVal()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, fnd As Range, rngList As Object, key As Variant
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set rngList = CreateObject("Scripting.Dictionary")
    For Each rng In Range("A1:A" & LastRow)
        If Not rngList.Exists(rng.Value) Then
            rngList.Add rng.Value, Nothing
        End If
    Next
    For Each key In rngList
        Set fnd = Range("A1:A" & LastRow).Find(key, after:=Cells(LastRow, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            MsgBox fnd.Offset(, 2)
        End If
    Next key
    Application.ScreenUpdating = True
End Sub

 

[dfmaniquiz]

Hello, I can't seem to make it work. Just to clarify, what i'm trying to do is not search all duplicate values. A specific or "given" cell in col A is selected then the script will look for a "previous" entry or duplicate of that value then show the corresponding column C value of that entry. Thank you

 

[mumps]

Try:

Sub FindVal()
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    MsgBox Range("A1:A" & LastRow).Find(ActiveCell.Value, after:=Cells(LastRow, 1), LookIn:=xlValues, lookat:=xlWhole).Offset(, 2)
End Sub

 

[dfmaniquiz]

Thank you so much
Works perfectly
Lastrow = .... needs after:=ActiveCell

 

[mumps]

You are very welcome.