Excel链接Hyperlinks 通过宏来生成和更新一些字段

Sub Macro1()
Call convertToEmail
End Sub

Sub convertToEmail()
    Dim convertRng As Range
    'Change the range to suit your need.
    Set convertRng = Range("B13:B160")
    Dim rng As Range
    Dim count As Integer
    count = 0

    For Each rng In convertRng
        If rng.Value <> "" Then
            'ActiveSheet.Hyperlinks.Add rng, "mailto:" & rng.Value
            count = count + 50
            ActiveSheet.Hyperlinks.Add rng, Address:="", SubAddress:="JobDetails!A" & count
        End If
    Next rng

End Sub

 

 

对可能引起歧义的工作表名称通常使用单引号引用它.

如 "'Job Details'"!A1

ActiveSheet.Hyperlinks.Add Anchor:=.Cells(x, 2), Address:="", SubAddress:="'" & Sheets(x).Name & "'!A1

 

 

有用的宏

Sub test()
    Dim convertRng As Range
    'Change the range to suit your need.
    Dim col As Integer
    Dim rng As Range
    Dim cellpas As String
   
    ' A - 65  0
    ' B - 66  1
    ' C - 67
    ' D - 68
    ' E - 69
   
    For col = 0 To 16
        cellpas = Chr(col + 65 + 4) + "111:"
        cellpas = cellpas + Chr(col + 65 + 4) + "345"
        Set convertRng = Range(cellpas)
        For Each rng In convertRng
            If rng.Value = "" Then
                ' ActiveSheet.Hyperlinks.Add rng, Address:="", SubAddress:="JobDetails!A" & count
                rng.Value = "NA"
            End If
        Next rng
    Next col

End Sub