loop through cells in named range

Question

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!

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

Answer 1

Set Rng =Range("A1:A3") is creating a range object, not a named range. This should work

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

Range("A1:A3).Name="Rng"

will create it or you can create and loop it like thsi

Dim c As Range
Range("a1:a3").Name = "rng"
For Each c In Names("rng").RefersToRange
MsgBox c.Address
Next c

Answer 2

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:

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.