Confused with variable

[Ray Batig]

Greetings,

I have a named range called WorksheetNames. I want to cycle through them and
do a set of operations. I wrote the following simplified code, however, I
can't seem to get the Name to work in the Worksheets() convention.

Dim Name As Range

For Each Name in Range("WorsheetNames")
Worksheet(Name).Cells(1,1).Value = "No Flag Set"
Other code
Next Name

Would someone explain what I am missing?

Thanks,
Ray

 

[Chip Pearson]

Ray,

"Name" is an object type in Excel/VBA, so you shouldn't use it as
a variable name. Also, you need Worksheets to be plural, not
singular. Try something like


Dim Rng As Range
For Each Rng In Range("WorksheetNames")
Worksheets(Rng.Value).Cells(1,1).Value = "No Flag Set"
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Vasant Nanavati]

1. "WorksheetNames" is mispelled.

2. The correct syntax is Worksheets(Name)

3. Also, since Name is a reserved VBA word, it may be better to use another
name (no pun intended) for your cell variable.

 

[Ray Batig]

Thanks Chip! Now I understand. The .Value was the crux.

Ray