Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Hyperlinks
I have a spreadsheet which contains over 1,000 hyperlinks to a website.
Many don't work because they are missing two semi-colons. Is there a quick way to check which ones have the missing semi-colons and to insert them. There are two places in the hyperlink where they should occur - always before &version and always at the end of the hyper link. Any pointers in the right direction would be greatly appreciated. Rusty |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Hyperlinks
Hi Bill,
Thank you so much - that worked like a charm. Cheers, Rusty "Bill Manville" wrote in message ... I would think something like this (which assumes all the hyperlinks are in the active worksheet and that all hyperlinks in the worksheet that contain "&version" need to be checked): Sub RepairHyperlinks() Dim H As Hyperlink Dim stAddr As String Dim iChar As Integer For Each H In ActiveSheet.Hyperlinks stAddr = H.Address iChar = InStr(stAddr, "&version") If iChar0 Then If Mid(stAddr,iChar-1,1)<";" Then stAddr=Left(stAddr,iChar-1) & ";" & Mid(stAddr,iChar) End If If Right(stAddr,1)<";" Then stAddr = stAddr & ";" H.Address = stAddr End If Next End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Hyperlinks
I would think something like this (which assumes all the hyperlinks are
in the active worksheet and that all hyperlinks in the worksheet that contain "&version" need to be checked): Sub RepairHyperlinks() Dim H As Hyperlink Dim stAddr As String Dim iChar As Integer For Each H In ActiveSheet.Hyperlinks stAddr = H.Address iChar = InStr(stAddr, "&version") If iChar0 Then If Mid(stAddr,iChar-1,1)<";" Then stAddr=Left(stAddr,iChar-1) & ";" & Mid(stAddr,iChar) End If If Right(stAddr,1)<";" Then stAddr = stAddr & ";" H.Address = stAddr End If Next End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks: Hyperlinks change on copy/paste? | Excel Worksheet Functions | |||
Update 2000 Excel hyperlinks to 2003 hyperlinks | Excel Worksheet Functions | |||
How toi turn-off hyperlinks [excel]? Email hyperlinks pop up ! | Excel Discussion (Misc queries) | |||
Excel Hyperlinks- cell content v. hyperlinks | Excel Discussion (Misc queries) | |||
hyperlinks | Excel Discussion (Misc queries) |