I have a column containing hyperlinks and I want to do a programmatic search and replace of the hyperlink address - the address itself, not the text to display. How do I do this?Answer1:
By modifying the "Address" property of the Hyperlinks element of a range object. Hyperlinks is a collection, so you will pick the first item if your range is a single cell, but it allows you to easily loop through all the links contained in a given range or worksheet.
Here's an example to change the link of the currently selected cell only (note: it must already have an hyperlink).
Sub ChangeLink() Application.ActiveCell.Hyperlinks(1).Address = "www.test.com" End Sub
Here's a variation that changes all links in the current worksheet to www.google.com, as long as they are not currently pointing to www.google.com (not necessary, but just for demonstration's sake):
Sub ChangeLinks() For x = 1 To Application.ActiveSheet.Hyperlinks.Count If Application.ActiveSheet.Hyperlinks(x).Address <> "www.google.com" Then Application.ActiveSheet.Hyperlinks(x).Address = "www.google.com" End If Next x End Sub
You can also add and delete links using the Hyperlinks.Add and .Delete methods. The TextToDisplay property allows you to change the text, and the Range property allows you to access (or figure out) which cells a given link belongs to.
EDIT: As requested, here's how to do it on all links in one specific column.
Most often I like to create a few variables to assign (Set) the objects I'm working with, such as the worksheet (calling "Sheet1" here), then the actual column which I will refer to by myColumn. If you get an error message, this allows you to pinpoint where it fails exactly.
Notice how the Hyperlinks collection adjusts itself nicely according to the object you call it from!
I also added a check that shows a message if there are no links in the specified column.
Sub ChangeLinksInColumn() Dim mySheet As Worksheet Dim myColumn As Range Set mySheet = Application.Worksheets("Sheet1") Set myColumn = mySheet.Columns(3) ' Enter column number here ' Check if column contains hyperlinks If myColumn.Hyperlinks.Count = 0 Then MsgBox "No hyperlinks in specified column." Else ' Perform task in myColumn only For x = 1 To myColumn.Hyperlinks.Count myColumn.Hyperlinks(x).Address = "www.google.com" Next x End If End Sub