Hyperlink cell

[Cooky857]

Hi I need help, I need a VBA that any cell in Column "E" that starts with “https:*” becomes an Active Hyperlink and displays as “Link to Site” and all cells do not become Hyperlinks and are unchanged.

I have tried a few methods (one below) but all convert all cells in E Column to Hyperlinks, Any help would be Great, more than happy to show more failed attempts but seams a but of a waste

Sub Links()

Dim link As Hyperlink
    For Each link In ActiveSheet.Hyperlinks
       link.TextToDisplay = "Voucher Link "
   Next link

  End Sub

 

[SQUIDD]

so some of your cells start with "https" and other do not?

you want to convert just the ones that do

 

[Cooky857]

so some of your cells start with "https" and other do not?

you want to convert just the ones that do

Hi Yes that it, if it helps there is approximately 150 rows and it’s a 50/50 split.

 

[SQUIDD]

Hi

Sorry for the delay, i had trouble with this one.

Hopefully this works for you, as it did for me, test this on a copy of your data.

assuming you have a header row also?

Sub convert_links()
lr = Range("E" & Rows.Count).End(xlUp).Row
For a = 2 To lr
    If Left(Range("E" & a), 5) = "https" Then Range("e" & a).Hyperlinks.Add Anchor:=Range("e" & a), Address:=Range("e" & a), TextToDisplay:="Voucher Link "
Next a
End Sub

 

[Cooky857]

Hi

Sorry for the delay, i had trouble with this one.

Hopefully this works for you, as it did for me, test this on a copy of your data.

assuming you have a header row also?

Sub convert_links()
lr = Range("E" & Rows.Count).End(xlUp).Row
For a = 2 To lr
    If Left(Range("E" & a), 5) = "https" Then
        With Range("E" & a).Hyperlinks
            Range("e" & a).Hyperlinks.Add Anchor:=Range("e" & a), Address:=Range("e" & a), TextToDisplay:="Voucher Link "
        End With
    End If
Next a
End Sub

 

[Cooky857]

Fantastic I’ll give it a go thanks will let you know asap

 

[SQUIDD]

please see below

this is all you actually need assuming it works

Sub convert_links()
For a = 2 To Range("E" & Rows.Count).End(xlUp).Row
    If Left(Range("E" & a), 5) = "https" Then Range("e" & a).Hyperlinks.Add Anchor:=Range("e" & a), Address:=Range("e" & a), TextToDisplay:="Voucher Link "
Next a
End Sub

 

[Cooky857]

please see below

this is all you actually need assuming it works

Sub convert_links()
For a = 2 To Range("E" & Rows.Count).End(xlUp).Row
    If Left(Range("E" & a), 5) = "https" Then Range("e" & a).Hyperlinks.Add Anchor:=Range("e" & a), Address:=Range("e" & a), TextToDisplay:="Voucher Link "
Next a
End Sub

Hi Squidd, just tested it works perfectly thanks very much

 

[SQUIDD]

Great

happy to help.

Dave