If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel losing hyperlinks when I sort the worksheet
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 |
#2
|
|||
|
|||
Excel losing hyperlinks when I sort the worksheet
On Nov 3, 3:24*pm, "Scott Bass" sas_l_739 at yahoo dot com dot au
wrote: 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 |
#3
|
|||
|
|||
Excel losing hyperlinks when I sort the worksheet
Jennifer wrote:
Column F Column E, I think Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
|
|||
|
|||
Excel losing hyperlinks when I sort the worksheet
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) "Scott Bass" sas_l_739 at yahoo dot com dot au wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|