Forum Discussion

Louie Warren's avatar
Louie Warren
Copper Contributor
Dec 05, 2017
Solved

Excel - mass changing hyperlinks

Using Microsoft Professional Plus 2013   I have a spreadsheet created from an Oracle query in TOAD.  The results are considered Detail.  I then insert a Summary sheet for each Detail sheet.  I do...
Macros & VBA
tips and tricks
  • Mark Fitzgerald's avatar
    Mark Fitzgerald
    Dec 22, 2017

    Recording a macro to change the hyperlink in one cell will give you:

     

    Range("E5").Select
Selection.Hyperlinks(1).SubAddress = "Detail!H3"

    To change every hyperlink in column E you will need to:

    1. Loop through each cell in column E
    2. Check if the cell has a hyperlink because trying to change a non-existent hyperlink will throw an error
    3. Change the hyperlink to something that can be expressed in a formula based on where the cell is within column E (i.e. its ROW)

    Based on your Dummy workbook it seems that hyperlink is to a cell two rows above in column H of the Detail sheet.  The following code limits the loop to the UsedRange because looping through all  1048576 is inefficient.

     

    Sub EditHyperlinks2()
Dim cel As Range
With ThisWorkbook.Sheets("Summary")
   For Each cel In Intersect(.[E:E], .UsedRange)
      If cel.Hyperlinks.Count > 0 Then
         cel.Hyperlinks(1).SubAddress = "Detail!H" & cel.Row - 2
      End If
   Next
End With
End Sub
Mark Fitzgerald's avatar
Mark Fitzgerald
Iron Contributor
Dec 22, 2017

Recording a macro to change the hyperlink in one cell will give you:

 

Range("E5").Select
Selection.Hyperlinks(1).SubAddress = "Detail!H3"

To change every hyperlink in column E you will need to:

  1. Loop through each cell in column E
  2. Check if the cell has a hyperlink because trying to change a non-existent hyperlink will throw an error
  3. Change the hyperlink to something that can be expressed in a formula based on where the cell is within column E (i.e. its ROW)

Based on your Dummy workbook it seems that hyperlink is to a cell two rows above in column H of the Detail sheet.  The following code limits the loop to the UsedRange because looping through all  1048576 is inefficient.

 

Sub EditHyperlinks2()
Dim cel As Range
With ThisWorkbook.Sheets("Summary")
   For Each cel In Intersect(.[E:E], .UsedRange)
      If cel.Hyperlinks.Count > 0 Then
         cel.Hyperlinks(1).SubAddress = "Detail!H" & cel.Row - 2
      End If
   Next
End With
End Sub
  • Louie Warren's avatar
    Louie Warren
    Copper Contributor
    Dec 22, 2017
    Thank you! I run this "report" every month and this will save me a lot of time.

    The conditional formatting highlight in the dummy file isn't working correctly, and it seems the 1st time you go to the hyperlink, it goes to the wrong place. Plainly, the value displayed in cell E4 of the summary, resides in cell G2 of the detail. The hyperlink for E4 should take you to cell H2 of the detail page. I know that's what you said, but I wanted to make sure what I thought matched your explanation. Thank you again!

Resources