VBA Code to Change Internal Hyperlink

[OilEconomist]

Thanks in advance for your assistance. I would like the code to change the column of data's hyperlinks. I have the data in Column B, and each entry points to various sheets and rows throughout the workbook.

For example cell B9 of the sheet "Summary", has the word "Dog" and if you click on Dog, it will go to "Sheet1" Cell E500. I would like to change the E to G in all of the cells within Column B of the sheet "Summary". The links for each cell go to different sheets and rows, but all go to column E and I want to change them all to G.

So just to be clear, these hyperlinks are located within the workbook. Not to some external website.

It will be starting in row 9 of column B, and through an arbitrary last row within column B.

 

[John_w]

Try this:

Public Sub Change_Hyperlinks()

    Dim cell As Range
    
    With Worksheets("Summary")
        For Each cell In .Range("B9", .Cells(.Rows.Count, "B").End(xlUp))
            cell.Hyperlinks(1).SubAddress = Replace(cell.Hyperlinks(1).SubAddress, "!E", "!G")
        Next
    End With
        
End Sub

 

[OilEconomist]

Try this:

Public Sub Change_Hyperlinks()

    Dim cell As Range
   
    With Worksheets("Summary")
        For Each cell In .Range("B9", .Cells(.Rows.Count, "B").End(xlUp))
            cell.Hyperlinks(1).SubAddress = Replace(cell.Hyperlinks(1).SubAddress, "!E", "!G")
        Next
    End With
       
End Sub

Thanks @John_w for your response and assistance.

With your solution, what happens is that the Hyperlink on Summary does not change, but where it's pointing to (cell in that sheet) gets replaced with what was in Cell B of "Summary"

So say for example cell B9 of the "Summary" sheet points to cell D107 on "Sheet1", what happens is:

Sheets("Summary").Range("B9") hyperlink does not change as it still points to Sheets("Sheet1").Range("D107"), but now Sheets("Sheet1").Range("D107") value has been replaced with the value from Sheets("Summary").Range("B9") and has a hyperlink which points back to it (Sheets("Summary").Range("B9")).

This actually useful also, but if you have time to see if you can modify your code to get it to where I can change where the original cell points to.

Also it only changes

 

[John_w]

I don't know why that happens. The macro should do what you want, i.e. change the hyperlink, not the cell it points to. How are you creating the hyperlinks?

An Excel Link to 'Place in This Document' has the format SheetName!Cell, or 'Sheet Name'!Cell, if the sheet name contains a space. The Dog example would be Sheet1!E500. The macro simply looks for "!E" in the hyperlink's SubAddress and changes it to "!G". Please run this macro and post the output so I can see the hyperlink in the Summary sheet's B9 cell:

Sub B9()
    With Worksheets("Summary").Range("B9").Hyperlinks(1)
        MsgBox "Address: " & .Address & vbCrLf & "SubAddress: " & .SubAddress
    End With
End Sub

 

[OilEconomist]

I don't know why that happens. The macro should do what you want, i.e. change the hyperlink, not the cell it points to. How are you creating the hyperlinks?

An Excel Link to 'Place in This Document' has the format SheetName!Cell, or 'Sheet Name'!Cell, if the sheet name contains a space. The Dog example would be Sheet1!E500. The macro simply looks for "!E" in the hyperlink's SubAddress and changes it to "!G". Please run this macro and post the output so I can see the hyperlink in the Summary sheet's B9 cell:

Sub B9()
    With Worksheets("Summary").Range("B9").Hyperlinks(1)
        MsgBox "Address: " & .Address & vbCrLf & "SubAddress: " & .SubAddress
    End With
End Sub

Apologies @John_w as you initial code was correct and you are correct. I retested it just as you gave me.

I think the issue was how the initial macro created the data (output was incorrect) or something meaning I started with an incorrect data set. Not totally sure because I always try to test the code as given and ensure it that nothing will need to be changed when I apply it. I basically take a stripped down version of the file so that if I need to share it, I can. Never the less, I marked post 2 correctly as your code in that post and as written here is correct:

Public Sub Change_Hyperlinks()

Dim cell As Range
   
    With Worksheets("Summary")
        For Each cell In .Range("B9", .Cells(.Rows.Count, "B").End(xlUp))
            cell.Hyperlinks(1).SubAddress = Replace(cell.Hyperlinks(1).SubAddress, "!E", "!G")
        Next
    End With
       
End Sub

 

[OilEconomist]

I don't know why that happens. The macro should do what you want, i.e. change the hyperlink, not the cell it points to. How are you creating the hyperlinks?

An Excel Link to 'Place in This Document' has the format SheetName!Cell, or 'Sheet Name'!Cell, if the sheet name contains a space. The Dog example would be Sheet1!E500. The macro simply looks for "!E" in the hyperlink's SubAddress and changes it to "!G". Please run this macro and post the output so I can see the hyperlink in the Summary sheet's B9 cell:

Sub B9()
    With Worksheets("Summary").Range("B9").Hyperlinks(1)
        MsgBox "Address: " & .Address & vbCrLf & "SubAddress: " & .SubAddress
    End With
End Sub

@John_w, for a step further, how would I replace the address and or address and sub address?

I tried the following and it did not work for the address:

Public Sub Change_Hyperlinks()

Dim cell As Range
  
    With Worksheets("Summary")
        For Each cell In .Range("B9", .Cells(.Rows.Count, "B").End(xlUp))
            cell.Hyperlinks(1).Address = Replace(cell.Hyperlinks(1).Address, "Sheet1", "Sheet2")
        Next
    End With
      
End Sub

Also when I run this, for the Address, it is blank and the Subaddress is "Sheet1!E500"

Sub B9()
    With Worksheets("Summary").Range("B9").Hyperlinks(1)
        MsgBox "Address: " & .Address & vbCrLf & "SubAddress: " & .SubAddress
    End With
End Sub

 

[John_w]

@John_w, for a step further, how would I replace the address and or address and sub address?

I tried the following and it did not work for the address:

Public Sub Change_Hyperlinks()

Dim cell As Range

With Worksheets("Summary")
For Each cell In .Range("B9", .Cells(.Rows.Count, "B").End(xlUp))
cell.Hyperlinks(1).Address = Replace(cell.Hyperlinks(1).Address, "Sheet1", "Sheet2")
Next
End With

End Sub

Also when I run this, for the Address, it is blank and the Subaddress is "Sheet1!E500"

Sub B9()
With Worksheets("Summary").Range("B9").Hyperlinks(1)
MsgBox "Address: " & .Address & vbCrLf & "SubAddress: " & .SubAddress
End With
End Sub

The Address property is only used for external links (files and web sites) and is empty for links to cells. I specified Address in the MsgBox to help diagnose the problem.

The SubAddress property specifies both the sheet name and the cell, separated by "!", so if you want to change a link to point to Sheet2 instead of Sheet1, use:

Public Sub Change_Hyperlinks_Sheet()

    Dim cell As Range
    
    With Worksheets("Summary")
        For Each cell In .Range("B9", .Cells(.Rows.Count, "B").End(xlUp))
            cell.Hyperlinks(1).SubAddress = Replace(cell.Hyperlinks(1).SubAddress, "Sheet1!", "Sheet2!")
        Next
    End With
        
End Sub

 

[OilEconomist]

The Address property is only used for external links (files and web sites) and is empty for links to cells. I specified Address in the MsgBox to help diagnose the problem.

The SubAddress property specifies both the sheet name and the cell, separated by "!", so if you want to change a link to point to Sheet2 instead of Sheet1, use:

Public Sub Change_Hyperlinks_Sheet()

    Dim cell As Range
   
    With Worksheets("Summary")
        For Each cell In .Range("B9", .Cells(.Rows.Count, "B").End(xlUp))
            cell.Hyperlinks(1).SubAddress = Replace(cell.Hyperlinks(1).SubAddress, "Sheet1!", "Sheet2!")
        Next
    End With
       
End Sub

That works! Thanks!