HELP!

[BurgrLvr]

This is what's happening to me. I have 3650 rows so this is not feasible to
type each correctly. Can anyone help?

Hyperlinks are removed or invalid after you sort cells that contain these
hyperlinks in Excel
View products that this article applies to.
This article was previously published under Q214328
Expand all | Collapse all
SYMPTOMS
In Microsoft Excel, if you sort a range of cells in a worksheet that
contains h...
In Microsoft Excel, if you sort a range of cells in a worksheet that
contains hyperlinks, the following problems may occur:

* Some of the hyperlinks may be removed. Although the blue, underlined
text appears in the cell, nothing happens when you click the link.
* Some of the hyperlinks may be changed so that they refer to the wrong
addresses.

For example, a hyperlink that previously referred to one Web page may
now refer to a different Web page.

Back to the top
CAUSE
This problem may occur if you copy and paste cells that contain hyperlinks,
and...
This problem may occur if you copy and paste cells that contain hyperlinks,
and then sort the cells.
Back to the top
RESOLUTION
If this problem occurs, manually correct the hyperlinks. To do this, use one
of...
If this problem occurs, manually correct the hyperlinks. To do this, use one
of the following methods:

* If Excel has removed the hyperlink from the cell, select the cell and
click Hyperlink on the Insert menu. Type the correct address in the Address
box, and then click OK.

Note If Excel 2007 has removed the hyperlink from the cell, select the
cell, and then click Hyperlink in the Links group on the Insert tab. In the
Insert Hyperlink dialog box, type the correct address into the Address box,
and then click OK.

-or-
* If the hyperlink refers to the wrong address, right-click the cell,
and then click Edit Hyperlink. In the Edit Hyperlink dialog box, correct the
address in the Address box, and then click OK.

Back to the top
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products
that ar...
Microsoft has confirmed that this is a problem in the Microsoft products
that are listed in the "Applies to" section.
Back to the top
APPLIES TO

* Microsoft Office Excel 2003
* Microsoft Excel 2002 Standard Edition
* Microsoft Excel 2000 Standard Edition
* Microsoft Excel 97 Standard Edition
* Microsoft Excel 2004 for Mac
* Microsoft Excel X for Mac
* Microsoft Excel 2001 for Mac
* Microsoft Excel 98 for Macintosh
* Microsoft Office Excel 2007

 

[Gord Dibben]

The article you posted shows how to make the corrections.

What other help do you need?


Gord Dibben MS Excel MVP

 

[BurgrLvr]

The solution they provide is not feasable for 3504 hyperlinks, to change them
individually. I tested it on a few and it does not work.

 

[Charabeuh]

Hello,

Perhaps something like this:

I assume your cells with hyperlink are in column D (D2 to D3651)

1) insert a new column at the right of the column containing the cells with
hyperlink.

2) put the following formula into each cell of the inserted column that match
your cells with hyperlink
into E2 =ExtractHyperLink(D2)
into E3 =ExtractHyperLink(D3) and so on to E3651
This will put into E the value of the address of each hyperlink.

2) sort your data including column E

3) Select the cells with hyperlink (from D2 to D3651)

4) then run the macro: ResetHyperLink()

5) delete column E

Does this help you to find a solution to your problem ?

The VBA code to put into a VBA module:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit

Public Function ExtractHyperLink(FromCell As Range) As String
Dim hp As Hyperlink
On Error GoTo Err_EHL_1

For Each hp In FromCell.Worksheet.Hyperlinks
If Not Intersect(FromCell, hp.Range) Is Nothing Then
ExtractHyperLink = hp.Address
Exit Function
End If
Next hp

Err_EHL_1:
ExtractHyperLink = ""
End Function

Sub ResetHyperLink()
Dim xR As Range
On Error GoTo Err_RHL_1

For Each xR In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xR, _
Address:=xR.Offset(0, 1).Value, _
TextToDisplay:=xR.Value
Next xR
Exit Sub

Err_RHL_1:
Resume Next

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[Charabeuh]

I made a stupid error in my first post
try this :

I assume your cells with hyperlink are in column D (D2 to D3651)

1) insert a new column at the right of the column containing the cells with
hyperlink.

2) Select the cells with hyperlink (from D2 to D3651)

3) then run the macro: ExtractHyperLink()

4) sort your data including column E

5) Select the cells with hyperlink (from D2 to D3651)

6) then run the macro: ResetHyperLink()

7) delete comumn E

NB: this will work with hyperlinks to the web or email addresses
this will not work with links into the workbook.

Does this help you to find a solution to your problem ?

The VBA code to put into a VBA module
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit

Sub ExtractHyperLink()
Dim hp As Hyperlink
Dim xr As Range
On Error GoTo Err_EHL_1

For Each xr In Selection
xr.Offset(0, 1) = ""
For Each hp In xr.Worksheet.Hyperlinks
If Not Intersect(xr, hp.Range) Is Nothing Then
xr.Offset(0, 1) = hp.Address
Exit For
End If
Next hp
Next xr

Err_EHL_1:
Resume Next
End Sub

Sub ResetHyperLink()
Dim xr As Range
On Error GoTo Err_RHL_1

For Each xr In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xr, _
Address:=xr.Offset(0, 1).Value, _
TextToDisplay:=xr.Value
Next xr
Exit Sub

Err_RHL_1:
Resume Next

End Su
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[BurgrLvr]

Thank you so much for your help.

In inserted a column to the right of Column E (cause my hyperlink column is
actually located in E) and did =ExtractHyperlink(E2) in column F

It comes back with
#NAME?

Any ideas? Thanks, Leisa

 

[Charabeuh]

Hello,

The two macros are not function. They don't have parameters.
And they don't return any value.
You cannot use them as you do with worksheet functions.
You cannot write =ExtractHyperlink(E2) and put it into cell.
You should follow point 1 to 7 in my previous mail.