Creating hyperlinks across a single sheet?

**CdrJameson** · 05-16-2013, 05:07 PM

Thanks for reading and many thanks for answering....I have been 'Googling' this all day,

I have got a large spreadsheet that is crying out for some hyperlinks to other locations in the spreadsheet. There are about 450 cells with unique values in the range G4:T36. These cells contain values that are used in elsewhere in the document; outside of the G4:T36 range. I have created some code that I would like to:

Find the matching instance of the cell value (there is only one other instance of the cell value)
Create a hyperlink in the original location (i.e. G4 to start with) that points to the other location of the value.
Repeat through G4:T36 until complete.

I have some code that doesn’t work

Sub createHyperlinks()

    For Each cell In Range("G4:T36")

        'find the other instance of the active cell in the range g4:t36
        Cells.Find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False).Activate

        'add the hyperlink
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _

        "Products!ActiveCell.Address", TextToDisplay:=ActiveCell.Value

    Next

End Sub

Although it compiles and in my head should work, it doesn’t. The formatting for teh code turned out a bit screwy, hope it makes sense to you) It does find the other instance of the value but it creates a hyperlink in that location, not up in the G4:T36 range where I need it. The first value in G4 is repeated at B49, The hyperlink is created at B49. The hyperlink doesn’t work as a hyperlink, it is blue and underlined but when I select it, it throws an error ‘the reference is invalid’. When I edit the hyperlink the cell that it refers to says A1 (which is not what I wanted) but if I then type in A1 the hyperlink works.