|
|
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
|
|
On Nov 3, 3:24 pm, "Scott Bass" <sas_l_739 at yahoo dot com dot au>
wrote:
[Quoted Text] > 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
|
|
Jennifer wrote:
[Quoted Text] > Column F
>
Column E, I think
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|
|
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
news:490f6c1e$0$22650$5a62ac22[ at ]per-qv1-newsreader-01.iinet.net.au...
[Quoted Text] > 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
>
|
|
|