Convert text to hyperlinks with VBA

[Ah5522]

Hi there,

I've looked around all over and cannot find the answer anywhere easily: I have thousands of text URL's that im trying to convert to clickable links in column B of my sheet (excel 2010). Now I know you can do this with a drag down of a formula =hyperlink ect , but I am trying to make a sheet that only uses userform buttons because the end user of the sheet just wants to be able to click a few buttons and generate a report.

I have found codes like the following:

[COLOR=#000000][FONT=Consolas]Sub Convert_To_Hyperlinks()[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Dim Cell As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell <> "" ThenActiveSheet.Hyperlinks.Add Cell, Cell.Value
End If
Next 
</code>[COLOR=#000000][FONT=Consolas]End Sub[/FONT][/COLOR]

My only issue with these is that they rely on the user to select column B before running the macro. Is there a way to code it to specify range? I gave it a go and end up turning all my data into Blue underlined text... Epic fail.

 

[Ah5522]

I think I just solved my own problem:

Sub CreateHyperlinks()Dim LR As Long, Rw As Long


LR = Range("B" & Rows.Count).End(xlUp).Row


For Rw = 2 To LR
    Range("B" & Rw).Hyperlinks.Add Range("B" & Rw), "" & Range("B" & Rw).Text
Next Rw


End Sub

 

[Fluff]

Hiya
This should do what you're after

    Dim Cell As Range

    For Each Cell In Range("B1", Range("B" & Rows.Count).End(xlUp))
        If Cell <> "" Then ActiveSheet.Hyperlinks.Add Cell, Cell.Value
    Next

 

[Poduska]

Thanks for the insight.

Just for FYI, I ran this from MSAccess to format a range in an XLS file I was building after I pasted in data from a recordset.
I happened to know it is always 4 rows so I just used the range but could do something dynamic.

xlRange is just the ranged variable I use for this process. xlBook is the declared workbook, xlSheet is the declared worksheet.

    Set xlRange = xlSheet.Range("I18:I22")
    Dim Rng As Range
    For Each Rng In xlRange
        xlBook.ActiveSheet.Hyperlinks.Add Rng, Rng.Value
    Next

[/QUOTE]