循环浏览Excel工作表中的所有命名范围 [英] Loop through all named ranges in a Excel Sheet

问题描述

我需要遍历工作表中的所有命名范围.
我目前正在这样做:

I need to loop through all named ranges in my sheet.
I am currently doing this:

For Each nm In ActiveWorkbook.Names

问题在于此工作表具有具有全局作用域和工作表作用域的名称.
此循环仅获取全局范围.
我尝试过:

The problem is that this sheet has names with global scope and with sheet scope.
This loop only gets the global scope.
I've tried:

For Each nm In Activeworkbook.Sheets(1).Names

但是无法使其正常工作.此循环还仅获取具有全局作用域的命名范围.想法?
我知道最好的解决方案是更改名称的范围，但我做不到.

But couldn't make it work. This loop also only gets those named ranges with global scope. Ideas?
I know the best solution would be to change the scope of the name, but I can't do it.

推荐答案

可以使其今天正常运行.要发布到这里，我使我的问题更加简单，但这不是一个好主意.

Could make it to work today. To post here, I made my problem much more simple, but this wasn't a good idea.

我真正要做的是从工作表中导入一些值(用application.getopenfilename选择并用workbooks.open打开). 因此，我在已导入"工作表中循环浏览所有名称，并将这些范围的值导入到原始工作表中具有相同名称的范围.

What I really do is to import some values from a sheet (selected with application.getopenfilename and opened with workbooks.open). So I loop through all names in this "imported" sheet and import the values of those ranges to ranges with the same name in my original sheet.

for each nm in thisworkbook.names
    if left(nm.name, 5) = "campo" then
        'here I make my copy
    end if
next nm

事实证明，当您拥有具有工作表范围的名称时，nm.name将返回类似以下内容的内容:

Turns out that when you have a name with sheet scope, nm.name returns something like this:

 nameOfSheet!nameOfField

因此，我永远也无法参与其中.为了解决我的问题，我使用了以下代码.谢谢大家对我的帮助.

So I could never get in to that if. To solve my problem I used the following line. Thank you all for trying to help me.

currentName = Mid(nm.Name, InStr(1, nm.Name, "!") + 1)

这篇关于循环浏览Excel工作表中的所有命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！