Identify cell address of first instance of duplicate value in vba

dfmaniquiz

New Member
Joined
Sep 19, 2019
Messages
5
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
When you get cell value "Apple", what do you want to do with it?
 
Upvote 0
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.
 
Upvote 0
See if this helps:
Code:
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
 
Upvote 0
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
 
Upvote 0
Try:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top