CODEWITHSUNDEEP
vbaexcelexcel-formula
user2727453
user2727453

Reputation: 77

Create a Hyperlink that searches worksheet and selects cell with duplicate contents

I have a value in a cell. This value is duplicated, intentionally, in another part of the worksheet. I would like to be able to click the cell in C5 with contents 12345 and it selects the cell in A1:1600 that contains the same value. I will never have more than 2 cells with this same value in the worksheet, but the values will change.

I appreciate any help you can offer.

Thank You.

Upvotes: 1

Views: 294

Answers (2)

ARich
ARich

Reputation: 3279

You can use the Hyperlink function to do what you wanting. But you would have to manually type out the formula for each cell that you wanted to link... Here's an example:

=HYPERLINK("[Book1]Sheet1!F2",12345)

This method is very unwieldy. The only way to do what you want in a robust fashion would be to use VBA.

Edit: I was able to duplicate the issue. The below edits seem to resolve the issue.

This VBA solution used the FindNext function to find the next value in the sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim FirstAddress As String
Dim Rng As Range
Dim x As Long
x = Me.UsedRange.Rows.Count

FirstAddress = Target.Address

Set Rng = Me.UsedRange.Find(Target.Value)

If FirstAddress = Rng.Address Then
    Me.UsedRange.FindNext(Rng).Select
Else
    Rng.Select
End If

End Sub

This works with a double click for the sheet the code is in, and it doesn't matter where the duplicate value is in that sheet. Just place the code in your worksheet's module.

One last way to do this (although still inferior to VBA) is to insert the hyperlink:

enter image description here

In this example, you click on A2>go to Insert Tab>Hyperlink>Place in This Document and enter the corresponding cell. This hyperlinks cell A2 to F2 so that when A2 is selected F2 is selected.

Upvotes: 0

John Bustos
John Bustos

Reputation: 19564

This should do the trick - I was unsure of the range you wanted to specify, so I just put it as A1:Z1600, but change it as necessary.

In VBA, paste this into your sheet's code module:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim OriginalAddress As String
Dim ValToFind As String
Dim CurrentCell As Range

    OriginalAddress = Target.Parent.Address
    ValToFind = Target.Parent.Value

    With Range("A1:Z1600")
        Set CurrentCell = .Find(What:=ValToFind)

        If OriginalAddress = CurrentCell.Address Then
            .FindNext(After:=CurrentCell).Activate
        Else
            CurrentCell.Activate
        End If
    End With
End Sub

Upvotes: 0

Related Questions