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

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

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