Validating hyperlinks and text for hyperlink

[Guest]

I have an issue where I have hyperlinks that do not match the text that's
shown for them. I think what happened is that someone copied the cells down
and just changed the text, but not the hyperlink.

Here is an example of the problem.

Hyperlink displayed: www.microsoft.com
Link coded: www.yahoo.com

How can we validate which hyperlinks match and which ones don't?

Thanks in advance.

Barb Reinhardt

 

[Earl Kiosterud]

Barb,

The following code will put the each hyperlink's address next to the cell.
And some other stuff which you can rem out. Insert four columns after your
column of hyperlinks before you run it.

Sub ValidateHyperlinks()
Dim link As Hyperlink

For Each link In ActiveSheet.Hyperlinks()
Cells(link.Range.Row, link.Range.Column).Offset(0, 1) = link.Name
Cells(link.Range.Row, link.Range.Column).Offset(0, 2) = link.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 3) = link.Range.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 4) = link.ScreenTip

Next link
End Sub

If you want the cell to show the URL, a similar loop could go through and
change them.

 

[Guest]

Can I do this without VBA?

Barb,

The following code will put the each hyperlink's address next to the cell.
And some other stuff which you can rem out. Insert four columns after your
column of hyperlinks before you run it.

Sub ValidateHyperlinks()
Dim link As Hyperlink

For Each link In ActiveSheet.Hyperlinks()
Cells(link.Range.Row, link.Range.Column).Offset(0, 1) = link.Name
Cells(link.Range.Row, link.Range.Column).Offset(0, 2) = link.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 3) = link.Range.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 4) = link.ScreenTip

Next link
End Sub

If you want the cell to show the URL, a similar loop could go through and
change them.

 

[Earl Kiosterud]

Barb,

I can't think of an automated way to check them, other than with VBA. You
could right-click each, click Edit Hyperlinks, and examine it manually
(address line).

Or click each, and see where it takes you. Not funny, I guess.

 

[Guest]

I found the getaddr() function and that will get me what I want. Thanks for
the info. You gave me enough info to know what to look for.