Macro to convert string to hyperlink

**Groovicles** · 07-09-2014, 01:38 PM

Hi everyone,

I have the following code:

Sub Apply_Hyperlinks1()
Dim r As Range
Dim myval As String
   For Each r In Range("K1:K3000")
      If InStr(r, "http://") > 0 Or InStr(r, "www.") > 0 Then
         myval = r.Value
            Range(r.Address).Select
               ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=myval, TextToDisplay:=myval
      End If
   Next r
   Range("K1").Select
End Sub

This code is supposed to go through column K and convert any test strings within each cell starting with http:// or www. from a string to hyperlink. It is possible that other text will be in the cell which should not be converted to a hyperlink; on text that starts with http:// or www.

When I run the code I get run-time error 13 and the if InStr line is indicated. Any ideas?

Thanks!

**Ron Coderre** · 07-09-2014, 02:33 PM

Is this code (from my stash) something you can use?

Sub ConvertTxt2Hyperlink()
Dim cCell As Range

For Each cCell In selection.Cells

   If cCell.Hyperlinks.Count = 0 Then
      On Error Resume Next
      ActiveSheet.Hyperlinks.Add _
         Anchor:=cCell, _
         Address:=cCell.Value, _
         TextToDisplay:=cCell.Value
   End If
Next cCell
End Sub

• Select a range of cells
• ALT+F8...Select: ConvertTxt2Hyperlink...Click: Run

**Groovicles** · 07-11-2014, 10:33 AM

hi Ron,

Thanks for the reply and your code. Unfortunately your code converts the entire text in the cell to a hyperlink. What I'm trying to do is work out a macro that will convert only text in the cell starting with http:// or www. into a hyperlink and leave the other text as text. Any ideas?

Thanks,