Range problem
 

I am currently writing an application that involves selecting a different
worksheet based on the choice made by the user. Here is my code so far. The
problem is with the set emp_range line. I keep on getting an "An application
defined or Object defined error" message. I pretty much copied the range code
from another application. (Changing certain things of course.)
What's wrong here?
Thanks,
David

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

Range problem
 

It works fine if this line

str = Worksheets("Commands").Cells(2, 4)

returns a valid worksheet name in the activeworkbook

--
HTH

Bob Phillips

"David Gerstman" wrote in message
...
I am currently writing an application that involves selecting a different
worksheet based on the choice made by the user. Here is my code so far.
The
problem is with the set emp_range line. I keep on getting an "An
application
defined or Object defined error" message. I pretty much copied the range
code
from another application. (Changing certain things of course.)
What's wrong here?
Thanks,
David

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

Range problem
 

Use the following

Sub test3()
Dim str As String
Dim c As Range

str = Worksheets("Commands").Cells(2, 4)

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

For Each c In emp_range
MsgBox c.Value
Next c
End With

End Sub


"David Gerstman" wrote:

I am currently writing an application that involves selecting a different
worksheet based on the choice made by the user. Here is my code so far. The
problem is with the set emp_range line. I keep on getting an "An application
defined or Object defined error" message. I pretty much copied the range code
from another application. (Changing certain things of course.)
What's wrong here?
Thanks,
David

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