遍历命名范围列表 [英] Loop through named range list
问题描述
我找到了很多例子,但就我而言,它不起作用,我也不知道为什么. 非常基本的代码:
I found a lot of examples but it's not working in my case and I don't know why. Very basic code:
Sub Test()
Dim namCur As Name
For Each namCur In ActiveSheet.Names
MsgBox "Name: " & namCur.Name & ", Refers To: " & namCur.RefersTo
Next namCur
End Sub
当我使用Worksheets("Assumptions").Names
当我观看ActiveSheet.Name
时,这是正确的,我得到了假设",您可以在图片上看到命名范围列表的下方.但是我从来没有得到MsgBox,For循环直接结束了.
非常重要,我只需要循环此工作表的命名范围,而不是整个工作簿
有什么主意吗?
When I watch ActiveSheet.Name
, this is correct I get "Assumptions", you can see on the picture below the list of named ranges. But I never get the MsgBox and the For loop goes directly to the end.
Very important, I need to loop only this sheet's named ranges, not the whole workbook
Any idea?
我使用Excel 2016
I use Excel 2016
推荐答案
您的解决方案将仅列出范围设置为仅ActiveSheet的名称.
Your solution will only list Names that have a scope set to only the ActiveSheet.
更改此
For Each namCur In ActiveSheet.Names
对此
For Each namCur In ThisWorkBook.Names
列出工作簿中的所有名称.然后,您可以检查RefersTo地址以检查它是否适用于ActiveSheet.
to list all names in the Workbook. You can then check the RefersTo address to check if it applies to the ActiveSheet.
Sub Test()
Dim namCur As Name
Dim TargetSheetName As String
TargetSheetName = "Assumptions"
For Each namCur In ThisWorkbook.Names
If Range(namCur.RefersTo).Parent.Name = TargetSheetName Then MsgBox "Name: " & namCur.Name & ", Refers To: " & namCur.RefersTo
Next namCur
End Sub
这篇关于遍历命名范围列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!