convert a cell to hyperlink

[vijehspaul]

Hi all,
looking to add hyperlink to a range of cell.
My requirement/situation is below:
1. each cell will fill with numbers (5-8 digits)
2. need to add hyperlink to same cell
3. the link address is as follows:
first part will be same for all cell ( eg: www.mysite.com/page/) + the cell value.
4. i want the cell value as 'Text to display'

I hope my question is clear, else please ask.


Thanks in advance,
Vjsh.

 

[Luke M]

How's this?

Sub MakeLinks()
Dim c As Range
Dim add As String
Dim myRange As Range
 
'Where are the cells?
Set myRange = Range("F5:F13")
 
Application.ScreenUpdating = False
For Each c In myRange
    'Store the cell value
    add = c.Value
    'Make the hyperlink
    ActiveSheet.Hyperlinks.add Anchor:=c, Address:= _
        "http://www.mysite.com/page/" & add
Next c
Application.ScreenUpdating = True
 
End Sub

 

[vijehspaul]

Thanks Luke.

A small concerns...
. Need to run this on background starting from file open and need to run this only if a cell have a value.

with your suggested macro, i have to run macro from developer options and on executing the macro am seeing the range filled like this:

http://mysite/
123456
http://mysite/
http://mysite/
http://mysite/
http://mysite/
http://mysite/
http://mysite/
http://mysite/



only the second cell have value and is converted to hyperlink as required but at the same time am seeing the incomplete site address throughout the Range.

please have a look.

Thanks.

 

[Luke M]

Why do you need it run every time the file opens?? After running the macro once, you should be ok. But, as you asked, this macro needs to be put in the ThisWorkbook module in order to work. Change the worksheet name and column range to suit.

Private Sub Workbook_Open()
Dim c As Range
Dim add As String
Dim myRange As Range
 
On Error Resume Next
'Where are the cells?
'Last part of this code selects only the cells with numbers
Set myRange = Worksheets("Sheet1").Range("F:F").SpecialCells(xlCellTypeConstants, 1)
 
On Error GoTo 0
If myRange Is Nothing Then Exit Sub 'No cells found
Application.ScreenUpdating = False
For Each c In myRange
    'Store the cell value
   add = c.Value
    'Make the hyperlink
   ActiveSheet.Hyperlinks.add Anchor:=c, Address:= _
        "http://www.mysite.com/page/" & add
Next c
Application.ScreenUpdating = True
End Sub

 

[vijehspaul]

Thanks Luke