Search and Goto Range(Target.Address).Value in column M

[omairhe]

Hello ,

Is there a way to jump to the duplicate value in column M. I want Vba to jump to the first instance of the duplicate value so that If the word "Thomas" is at M5 and at M20, I'd like to be taken to the top value which is M5. By taken I mean to scroll to the cell's row. Please also note that the column M will only have one duplicate entry and more than one duplicate entry is not possible. Also kindly note that I want to scroll Row only and not the column.

Will appreciate and much thanks.

 

[omairhe]

Hello ,

Is there a way to jump to the duplicate value in column M. I want Vba to jump to the first instance of the duplicate value so that If the word "Thomas" is at M5 and at M20, I'd like to be taken to the top value which is M5. By taken I mean to scroll to the cell's row. Please also note that the column M will only have one duplicate entry and more than one duplicate entry is not possible. Also kindly note that I want to scroll Row only and not the column.

Will appreciate and much thanks.

Sorry about the title. as my duplicate value is also target address's value. I'm just interested in the results..

 

[Fluff]

Will the duplicate always be a specific value?

 

[omairhe]

Will the duplicate always be a specific value?

No these will be different values.. but at a given moment only one duplicate will occur.

 

[Fluff]

Ok, how about

Sub omairhe()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("M2", Range("M" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl
         Else
            .Item(Cl.Value).Activate
            Exit Sub
         End If
      Next
   End With
End Sub

 

[omairhe]

Ok, how about

Sub omairhe()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("M2", Range("M" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl
         Else
            .Item(Cl.Value).Activate
            Exit Sub
         End If
      Next
   End With
End Sub

In Freezepanes this will also scroll the columns. But I like it and no problems.
Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[omairhe]

Ok, how about

Sub omairhe()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("M2", Range("M" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl
         Else
            .Item(Cl.Value).Activate
            Exit Sub
         End If
      Next
   End With
End Sub

Dear Fluff, after usage in other sheet I come across a problem with the way your given code is working. For example, when there are empty cells in between. As promised only one instance of duplicate will be present in my list , however I was not considering the empty cells in between the list. Could you please make the necessary adjustment to your otherwise fabulous code please to run with empty cells in between my list of column M?

Thanks a lot.

 

[Fluff]

Try

If Not .Exists(Cl.Value) And Cl.Value <> "" Then

 

[omairhe]

Try

If Not .Exists(Cl.Value) And Cl.Value <> "" Then

Excellent..
Thanks again.