Excel losing hyperlinks when I sort the worksheet

[Scott Bass]

Hi,

(Sorry for the crossposting, but I felt this post applied to both
newsgroups...assuming excel.links means "hyperlinks")

I have an Excel worksheet with about 1000 rows. One of the columns contains
hyperlinks. The text of the hyperlink is the URL - IOW the displayed text
and hyperlink are the same.

When I add new data to the worksheet, I need to sort it by a particular
column. When I do so, some of the hyperlinks are removed. The text is
still blue, underlined, proper font, but the hyperlink is gone (it's a very
hard problem to find).

This happens in both Excel 2003 and Excel 2007.

Has anyone ever seen this behavior, and know of a workaround?

Alternatively, is there an Excel function that will create a hyperlink from
the text in a cell? If so, can you also provide the VB code to:

* spin thru all the rows in the workbook
* for the text in column <whatever>, apply the <function> to the cell value
to convert it to a hyperlink

Sorry, while I'm a programmer and can follow and modify VB code, I'm not
fluent in VB so find it hard to create from scratch.

Thanks for the help!

Cheers,
Scott

 

[Jennifer]

Hi,

(Sorry for the crossposting, but I felt this post applied to both
newsgroups...assuming excel.links means "hyperlinks")

I have an Excel worksheet with about 1000 rows.  One of the columns contains
hyperlinks.  The text of the hyperlink is the URL - IOW the displayed text
and hyperlink are the same.

When I add new data to the worksheet, I need to sort it by a particular
column.  When I do so, some of the hyperlinks are removed.  The text is
still blue, underlined, proper font, but the hyperlink is gone (it's a very
hard problem to find).

This happens in both Excel 2003 and Excel 2007.

Has anyone ever seen this behavior, and know of a workaround?

Alternatively, is there an Excel function that will create a hyperlink from
the text in a cell?  If so, can you also provide the VB code to:

* spin thru all the rows in the workbook
* for the text in column <whatever>, apply the <function> to the cell value
to convert it to a hyperlink

Sorry, while I'm a programmer and can follow and modify VB code, I'm not
fluent in VB so find it hard to create from scratch.

Thanks for the help!

Cheers,
Scott

You could put some code in a loop to set the hyperlink value to be
what is the text in the cell like this (assuming Sheet 1 is the
worksheet name and your hyperlinks are in Column F) :

Dim X As Integer

For X = 1 To 1000
If Sheet1.Cells(X, 5).Value <> "" Then
Sheet1.Hyperlinks.Add Anchor:=Sheet1.Cells(X, 5), Address:= _
Sheet1.Cells(X, 5).Value, TextToDisplay _
:=Sheet1.Cells(X, 5).Value
End If
Next X
End Sub

 

[Bill Manville]

Column F

Column E, I think

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

 

[rslc]

Hi
this formula will create a clickable link using contents of cell c17 as
the search criteria
not sure if it is what you want but maybe you can modify it to suit
good luck rslc

=HYPERLINK("#'sheetname1'!a" & MATCH(C17,sheetname1!A1:A500,0), C17)