Results 1 to 9 of 9

Thread: Identify cell address of first instance of duplicate value in vba
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Identify cell address of first instance of duplicate value in vba

    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.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,257
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

    When you get cell value "Apple", what do you want to do with it?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

    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.

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

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

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,257
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

    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

  7. #7
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,257
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

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

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,257
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Identify cell address of first instance of duplicate value in vba

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •