另外还有一个值得注意的地方是，当你在循环name的时候，你观察name的referstorange属性，并不是名称管理器中名称的范围，而始终是单个的单元格，我总结为这也就是为什么名称引用不会像单元格A1C1样式引用那样会发生偏移错误吧，个中原理我就没再深究
Public Sub addname()
Dim wb As Workbook
Dim nm As Name
Dim Rng As Range, Tng As Range, c, r
Dim str As String
Dim regex As Object
Dim matches As Object, cel
Dim match As Object, txt As String
On Error Resume Next
Set regex = CreateObject("VBScript.RegExp")
With regex
.Pattern = "([A-Z]+[0-9]+)"
.Global = True
End With
' 获取当前活动的工作簿
Set wb = ActiveWorkbook
Set Rng = Range(Selection, Selection.End(xlDown))
n = 0
' 遍历每个名称管理器
For Each Tng In Rng
Set matches = regex.Execute(Tng.Formula)
For Each cel In matches
For Each nm In wb.Names
If nm.Visible = False Then
GoTo line
End If
If Not Intersect(Range(cel), nm.RefersToRange) Is Nothing Then
Tng.Formula = Replace(Tng.Formula, cel, nm.Name)
End If
line:
Next
Next
Next
End Sub
沪公网安备 31011702000001号 沪ICP备11019229号-2
