查找给定单元所属的命名范围 [英] Find the named ranges a given cell belongs to
本文介绍了查找给定单元所属的命名范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
现在我还要显示包含单元格的命名范围的名称。我想我可以扫描ActiveWorkbook的一些名称集合,并用相交测试但是有没有更简单的方法?
解决方案
最简单的方法是循环浏览名称。
函数getRangeNames(目标为范围)
Dim n As Name
Dim s As String
For This n In ThisWorkbook。名称
在错误恢复下一步
如果不相交(目标,n.RefersToRange)不是,然后
s = s&名词&
如果
出现错误GoTo 0
下一步n
getRangeNames = Left(s,Len(s) - 2)
结束函数
I'm writing a personal macro that displays important info about the ActiveCell on the statusbar, like the names (if any) of tables or pivottables it belongs to, what unformatted value it holds and such.
Now I would also like to display the names of the named ranges the cell is included in. I guess I could scan through some Names Collection for the ActiveWorkbook and test with intersect, but is there any easier way?
解决方案
The easiest way is to loop through the names.
Function getRangeNames(Target As Range)
Dim n As Name
Dim s As String
For Each n In ThisWorkbook.Names
On Error Resume Next
If Not Intersect(Target, n.RefersToRange) Is Nothing Then
s = s & n.Name & ", "
End If
On Error GoTo 0
Next n
getRangeNames = Left(s, Len(s) - 2)
End Function
这篇关于查找给定单元所属的命名范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文