EXCEL-VBA超链接转换查询

debugcn 发表于 Dev

用户2519726

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

嗨，我上面的代码可以将列转换为超链接。如您所见，每次单击按钮时它的效率都相当低，它会返回并将所有内容再次转换为超链接，即使那些已经是超链接的链接也是如此。请指出正确的方向。我需要一种方法来检测已经具有超链接偏移量为1的列，然后转换非超链接单元格。

提前致谢。

共产国际

只需尝试从单元格获取地址，然后检查是否收到错误：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。