jkluio668
2/20/2018 - 1:34 PM

hyperlink_add1


'----------
'[1]添加“r_num    jump”这样的hyperlink
'选中r_num的单元格或多个单元格,在其后一列生成跳转的jump,并在选中的单元格的数加5的单元格,添加跳转,并添加反向的jump

Sub hyperlink_add1()
    i1 = Selection.Row
    j1 = Selection.Column
    i_all = Selection.Rows.Count
    For i = 1 To i_all
'        Cells(i1 + i - 1, j1 + 1).Select
        hyperlink_add2 i1 + i - 1, j1 + 1, Cells(i1 + i - 1, j1) + 5
        hyperlink_add2 Cells(i1 + i - 1, j1) + 2, 1, i1 + i - 1, j1 + 1
    Next
End Sub
Function hyperlink_add2(r_d, c_d, r_t, Optional c_t = 1, Optional text = "jump", Optional ByVal sh = "")
    If sh = "" Then sh = ActiveSheet.Name
    Sheets(sh).Hyperlinks.Add Anchor:=Range(convert1(c_d) & r_d), Address:="", SubAddress:=sh & "!" & convert1(c_t) & r_t, _
    ScreenTip:="", TextToDisplay:=text
End Function

Function convert1(a) 'auto convert col_s or col_num
    If IsNumeric(a) Then
        convert1 = Split(Cells(1, a).Address, "$")(1)
    Else
        convert1 = Range(a & "1").Column
    End If
End Function