Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Validating hyperlinks and text for hyperlink
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 |
#2
|
|||
|
|||
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 www.smokeylake.com "Barb Reinhardt" wrote in message ... 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 |
#3
|
|||
|
|||
Can I do this without VBA?
"Earl Kiosterud" wrote: 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 www.smokeylake.com "Barb Reinhardt" wrote in message ... 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 |
#4
|
|||
|
|||
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. -- Earl Kiosterud www.smokeylake.com "Barb Reinhardt" wrote in message ... Can I do this without VBA? "Earl Kiosterud" wrote: 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 www.smokeylake.com "Barb Reinhardt" wrote in message ... 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 |
#5
|
|||
|
|||
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. "Earl Kiosterud" wrote: 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. -- Earl Kiosterud www.smokeylake.com "Barb Reinhardt" wrote in message ... Can I do this without VBA? "Earl Kiosterud" wrote: 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 www.smokeylake.com "Barb Reinhardt" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks and sorting data | Excel Discussion (Misc queries) | |||
Random hyperlinks everywhere! | Excel Discussion (Misc queries) | |||
Convert entire columns of text email addresses to hyperlinks | Excel Worksheet Functions | |||
Creating multiple hyperlinks from a column where the text in the . | Excel Worksheet Functions | |||
Creating multiple hyperlinks from a column where the text in the . | Excel Worksheet Functions |