vb macro to convert string to hyperlink 2

[oliviaF]

I am trying to convert a string stored in a cell to a hyperlink. The string contains the location (ie "C:/temp&quot that I wish to link to.
I also want the string to remain and be he handle for the hyperlink.
There are ~1000 cells so I want to be able to use a loop to convert each cell in the range to a hyperlink.

Can this be achieved. Sample code would be appreciated

 

[Nikita6003]

Hiya,

this'll add a hyperlink to the selected cell:

    Dim l_wksLink As Worksheet
    
    'Set sheet object
    Set l_wksLink = ThisWorkbook.Sheets(1)
    'Activate sheet to allow you to select cells
    l_wksLink.Activate
    'Select cell to b hyperlinked
    l_wksLink.Range("A2").Select
    'Add hyperlink
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=l_wksLink.Range("A2").Text
    
    'Release sheet object
    Set l_wksLink = Nothing

Just adapt to loop thru' your worksheet

HTH

Cheers
Ellen

 

[andrew299]

This works, there may be faster ways though

Sub hyperlink()
Dim codes$

Range("a1&quot.Select 'select the column you want

Do Until ActiveCell.Value = ""

code$ = ActiveCell.Value

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & code$ & "'!A1"
ActiveCell.Offset(1, 0).Select


Loop

End Sub

 

[oliviaF]

Thanks for your help
I ended up using a cross of both your codes to develop:

Sub hyperlink_adder()
Range("a1&quot.Select 'select the column you want
Do Until ActiveCell.Value = ""
'Add hyperlink
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
End Sub