Help changing hyperlink code to change cell references.

[Demention]

Hi all,

Thanks for having a look at this.

I have a spreadsheet with about 1000 hyperlinks, all of which I want to change the cell reference of. Each link has different text to display. I have found some code to change the screentip and was hoping that there's a way to rework it to change the cell reference in all hyperlinks to "A1"

Sub Change_ScreenTip()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    For Each r In ActiveSheet.UsedRange
        If r.Hyperlinks.Count > 0 Then
            r.Hyperlinks(1).ScreenTip = " "
        End If
    Next
Next
End Sub

Highlighting them all, clicking edit hyperlink and then OK just makes them all show the same table, where each hyperlink should show it's own unique table.

If anyone can help, that would be awesome.

Thanks in advance!

 

[Chihiro]

You'd change ".Address" property of ".Hyperlink" object.
https://msdn.microsoft.com/EN-US/library/office/ff835563.aspx

 

[Demention]

Thanks for the tip Chihiro.

This seems to have done the job and hasn't broken anything.

Sub Change_SubAddress()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    For Each r In ActiveSheet.UsedRange
        If r.Hyperlinks.Count > 0 Then
            r.Hyperlinks(1).SubAddress = "Cover!A1"
        End If
    Next
Next
End Sub