vba 循环遍历命名范围内的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13039437/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
loop through cells in named range
提问by Christi French
I am trying to write code that will loop through all cells in a range. Eventually I want to do something more complicated, but since I was having trouble I decided to create some short test programs. The first example works fine but the second (with a named range) doesn't (gives a "Method Range of Object_Global Failed" error message). Any ideas as to what I'm doing wrong? I'd really like to do this with a named range... Thanks!
我正在尝试编写将遍历范围内所有单元格的代码。最终我想做一些更复杂的事情，但由于遇到了麻烦，我决定创建一些简短的测试程序。第一个示例工作正常，但第二个（具有命名范围）没有（给出“Object_Global 的方法范围失败”错误消息）。关于我做错了什么的任何想法？我真的很想用命名范围来做到这一点......谢谢！
Works:
作品：
Sub foreachtest()
Dim c As Range
For Each c In Range("A1:A3")
MsgBox (c.Address)
Next
End Sub
Doesn't work:
不起作用：
Sub foreachtest2()
Dim c As Range
Dim Rng As Range
Set Rng = Range("A1:A3")
For Each c In Range("Rng")
MsgBox (c.Address)
Next
End Sub
回答by Charles Williams
Set Rng =Range("A1:A3") is creating a range object, not a named range. This should work
Set Rng =Range("A1:A3") 正在创建范围对象，而不是命名范围。这应该工作
Sub foreachtest2()
Dim c As Range
Dim Rng As Range
Set Rng = Range("A1:A3")
For Each c In rng
MsgBox (c.Address)
Next
End Sub
If you want to create a Named Range called Rng then
如果你想创建一个名为 Rng 的命名范围，那么
Range("A1:A3).Name="Rng"
will create it or you can create and loop it like thsi
将创建它，或者您可以像 thsi 一样创建和循环它
Dim c As Range
Range("a1:a3").Name = "rng"
For Each c In Names("rng").RefersToRange
MsgBox c.Address
Next c
回答by nutsch
To adjust your second code, you need to recognize that your range rng is now a variable representing a range and treat it as such:
要调整您的第二个代码，您需要认识到您的范围 rng 现在是一个表示范围的变量，并将其视为这样：
Sub foreachtest2()
Dim c As Range
Dim Rng As Range
Set Rng = Range("A1:A3")
For Each c In rng
MsgBox (c.Address)
Next
End Sub
Warning: most of the time, your code will be faster if you can avoid looping through the range.
警告：大多数情况下，如果您可以避免在范围内循环，您的代码会更快。
回答by David
Try this, instead:
试试这个，而不是：
Sub foreachtest2()
Dim c As Range
Range("A1:A3").Name = "Rng"
For Each c In Range("Rng")
MsgBox (c.Address)
Next
End Sub
