Not sure why Jim turned this into a discussion of range names, but based on
your reference,
Your code was this:
Sub test3()
Dim str As String
str = Worksheets("Commands").Cells(2, 4)
Dim emp_range As Range
Set emp_range = Worksheets(str).Range("a1", Range("a2").End(xlDown))
For Each c In emp_range
MsgBox c.Value
Next c
End Sub

In a sheet module this is your problem - you are referring to ranges on two
different sheets in this line

Set emp_range = Worksheets(str).Range("a1", Range("a2").End(xlDown))

Thus your error.

in a sheet module, the unqualified Range("a2") refers to the sheet
containing the code - but str used in the qualification of the first range
is probably not the sheet containing the code. (also str is an excel
function, so using it as a variable is not wise) .

? typename(str(1233))
String
converts a number to a string

When you move it to the thisworkbook module Apparently it isn't a problem.
Nonetheless, as a general rule, all your code should be in general modules
(insert=Module). Class modules such as Sheet modules and the Thisworkbook
module should be reserved for events associated with those objects. In
those cases, you can have excel enter the declaration from selecting the
object in the left dropdown at the top of the module and the event from the
right dropdown at the top of the module. Certainly this is a matter of
personal style, but I would highly recommend it to avoid problems like you
had.

--
Regards,
Tom Ogilvy


"David Gerstman" wrote in message
...
<a

href="http://www.microsoft.com/office/community/en-us/default.mspx?dg=micros
oft.public.excel.programming&mid=127739c5-cb43-423a-b963-855688533e0f&sloc=e
n-us"Earlier</a
I had asked about a Range problem. Eventually, I played around and solved
it
simply by moving the macro from being associated to one of the worksheets
to
being associated with "This Workbook."
I don't understand why this made a difference since I was referred to
specific worksheets in the code.
This begs a few questions:
1) Is it better form to associate a macro with a specific worksheet or to
the workbook?
2) If I'm associating the macor to a worksheet what are the limitations?
Thanks,
David