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