|
楼主 |
发表于 2020-3-4 10:18
|
显示全部楼层
Public a1, b1, c1, d1
Function GBDW(Optional a = "", Optional b = "", Optional c = "", _
Optional d = "", Optional e = "") '自定义超链接公式
If a = "" Then a1 = 2 Else a1 = a
b1 = b: c1 = c: d1 = d
If e = "" Then e1 = IIf(a1 = 1, "返回首行", "最后数据") Else e1 = e
GBDW = e1
End Function
Sub gbdws(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Row < 5 And Target.Column > 8 And Target.Column < 12 Then '这一句修改为 If Target.Row < 5 Then 后,光标还可以对文本类数据进行定位,功能强多了!
If Mid(Target.Formula, 1, 5) = "=GBDW" Then '判断是否为超链接公式!
x = Target.Address(0, 0): Set Rng = Range(x)
If b1 = "" Then b = Rng.Row + 1 Else b = b1
If c1 = "" Then c = Rows.Count Else c = c1
If d1 = "" Then d = Rng.Column Else d = Cells(1, d1).Column
If b = c Then b = b + 1
m = Range(Sh.Cells(c, d), Sh.Cells(b, d).Address(0, 0)).Find("*", _
LookIn:=-4163, SearchOrder:=1, SearchDirection:=2).Row
If a1 = 1 Then '建立超链接
Sh.Hyperlinks.Add Sh.Range(x), "#" & Cells(c, d).Address(0, 0)
Else
Sh.Hyperlinks.Add Sh.Range(x), "#" & Cells(m + 1, d).Address(0, 0)
End If
Else
Set t = Target.Dependents '相关单元格
For Each r In t
s = r.Formula: Range(r.Address).Formula = s
Next
End If
Else: Exit Sub
End If
End Sub
|
|