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 ```

Accepted Answer

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.

loop through cells in named range

Solution

Problem