Convert Strings to Hyperlinks (Excel VBA)

[ajetrumpet]

Here is a quick way to turn a cell's string into a hyperlink with Excel VBA:

            Range(CELLNAME).Select
               ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                                  Address:=THE ADDRESS, _
                                                  TextToDisplay:=LINK TEXT

I had to do this once from an online PHP report that I copied into Excel. The hyperlinks from the browser copied as strings, so this function changed them accordingly:

Sub Rearrange()

Dim r As Range
Dim myvalue As String

   For Each r In Range("a1", Range("a1").End(xlDown))
      If InStr(r, "http://") > 0 Or InStr(r, "www.") > 0 Then
         myvalue = r.Value
            Range(r.Address).Select
               ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                                  Address:=myvalue, _
                                                  TextToDisplay:=myvalue
      End If
   Next r

    Range("a1").Select

End Sub