EXCEL-VBA hyperlinks conversion inquiry

Question

Dim RITMRow As Long
Dim ws1 As Worksheet
Dim RITMstorage As String
Dim LastRow As Long




Set ws1 = Sheets("Tracker")


LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row

For RITMRow = 2 To LastRow


RITMstorage = ws1.Range("A" & RITMRow).Value



ws1.Range("A" & RITMRow).Hyperlinks.Add Anchor:=ws1.Range("A" & RITMRow), _
    Address:="https://site.site.com/sc_req_item.do?sys_id=" & RITMstorage, _
    ScreenTip:="Request Number", _
    TextToDisplay:=RITMstorage


Next RITMRow


With ws1

.Cells.Font.Size = "8"
.Cells.RowHeight = 11.25
.Cells.Font.Name = "Calibri"
.Range("A1").EntireRow.RowHeight = 25

End With

hi, my code above works in converting a column to hyperlinks. as you can see, it's quite a bit inefficient as everytime i click the button, it goes back and converts everything to hyperlinks again, even those that are already hypelinks. please point me in the right direction. i need a way to detect the columns that already has a hyperlink the offset by 1 then convert the non hyperlink cell.

thanks in advance.

score 1 · Accepted Answer · answered Apr 17 '15 at 03:09

1

Just try to get the address from the cell and check to see if you get an error:

Dim url As String
Dim isLink As Boolean
For RITMRow = 2 To LastRow

    On Error Resume Next
    url = ws1.Range("A" & RITMRow).Hyperlinks(1).SubAddress
    isLink = (Err.Number = 0)
    On Error GoTo 0

    If Not isLink Then
        RITMstorage = ws1.Range("A" & RITMRow).Value
        ws1.Range("A" & RITMRow).Hyperlinks.Add Anchor:=ws1.Range("A" & RITMRow), _
            Address:="https://site.site.com/sc_req_item.do?sys_id=" & RITMstorage, _
            ScreenTip:="Request Number", _
            TextToDisplay:=RITMstorage
    End If

Next RITMRow

answered Apr 17 '15 at 03:09

Comintern

21,855
5
33
80

hey! it works! i just want to ask what hyperlinks(1).subaddress does? thanks! – user2519726 Apr 17 '15 at 03:50
I tries to get the SubAddress of the first hyperlink in the cell. If there isn't one, it errors. I use it out of habit - Hyperlinks.Count would probably also work in most situations (assuming that you're working with a known Worksheet). – Comintern Apr 17 '15 at 03:57
@user2519726 ...and I just remembered the reason. See [this post](http://stackoverflow.com/questions/14422003/macro-to-open-excel-hyperlink-does-not-work-when-hyperlink-generated-with-a-form). – Comintern Apr 17 '15 at 04:08

EXCEL-VBA hyperlinks conversion inquiry

1 Answers1