display text from a link

[Ronald (Gmail)]

Hi all,

trough this forum I resolve the issue how to make a link from text lines

with a macro.

Example: the data loaded from a text file was

COLUMN A COLUMN B
www.microsoft.com link to microsoft
www.playboy.com nice pictures
www.google.com search engine

with the following macro the text in column A was set to link so
klicking on it will open the browser and open the displayed addres.
First selecting the wanted cells.
---------------------------------------
Sub AddHperLinks()
Dim cl As Range

For Each cl In Selection.Cells
ActiveSheet.Hyperlinks.Add Anchor:=c1, Address:=CStr("http://" &
cl.Value)

Next cl
---------------------------------------

What I need now is that the the text on column B changed in a link so
that klickin on it will bring me to the address as mentioned in column A
.. I intended t o delete column A after everithing is changed.
Using TextToDisplay may be a solution but I don't know how to place it
and make it working when loading the above example.

Appreciate any help !!

Best regards,

Ronald.

 

[Dave Peterson]

I think I'd hide columns A and B and use a worksheet formula in column A:

=HYPERLINK("http://"&A1,B1)

But this may help. I selected my range in column A and did the work there--I'd
want to delete column B when I was done.

Option Explicit
Sub AddHperLinks()
Dim cl As Range
For Each cl In Selection.Cells
ActiveSheet.Hyperlinks.Add Anchor:=cl, _
Address:="http://" & cl.Value, _
TextToDisplay:=cl.Offset(0, 1).Value
Next cl
End Sub

And watch out. Sometimes you type C1 (c-one) and sometimes (cl c-ell). They're
different!