Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
The article you posted shows how to make the corrections.
What other help do you need? Gord Dibben MS Excel MVP On Fri, 19 Feb 2010 13:59:01 -0800, BurgrLvr wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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. "Gord Dibben" wrote: The article you posted shows how to make the corrections. What other help do you need? Gord Dibben MS Excel MVP On Fri, 19 Feb 2010 13:59:01 -0800, BurgrLvr wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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 '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''' "BurgrLvr" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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 Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''' "BurgrLvr" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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" wrote: 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 Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''' "BurgrLvr" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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. "BurgrLvr" wrote: 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" wrote: 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 Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''' "BurgrLvr" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|