defining a range
 

In the following code below, I want to copy a number of items from
Worksheet "Items" to Worksheet "Rooms" based on the value of txtRoom.

What am I doing wrong when I set "listrange" in the select statement?


I get a "Method 'range' of object '_global' error"


Thanks,


David


Worksheets("Rooms").Activate
ActiveSheet.Range("A8:A17").Select
Selection.ClearContents
Selection.Rows.RowHeight = 36


Worksheets("Items").Activate
Set itemrange = Worksheets("Items").Range("a1",
Range("a1").End(xlToRight))
For Each ir In itemrange
If ir.Text = txtRoom Then
Exit For
End If
Next ir
Worksheets("Items").Activate
Select Case txtRoom


Case "Room1"


Set listrange = Worksheets("Items").Range(ir, Range(ir).End(xlDown))
listrange.Copy Destination:=Worksheets("SF 701").Range("a8")


Case "Room2"
....


Case "Room3"
....


Case "Room4"
....


End Select

defining a range
 

ir is a range. The default property is it's value so what you have is the
same as

Set listrange = Worksheets("Items").Range(ir, Range(ir.Value).End(xlDown))
and the value of ir is the contents of the first cell in the range.

try this...

Set listrange = Worksheets("Items").Range(ir, ir.End(xlDown))

if you have decalred ir as a range then you will get the intellisence list
of properties and methods for ir.
Dim ir as Range

--
HTH...

Jim Thomlinson


" wrote:

In the following code below, I want to copy a number of items from
Worksheet "Items" to Worksheet "Rooms" based on the value of txtRoom.

What am I doing wrong when I set "listrange" in the select statement?


I get a "Method 'range' of object '_global' error"


Thanks,


David


Worksheets("Rooms").Activate
ActiveSheet.Range("A8:A17").Select
Selection.ClearContents
Selection.Rows.RowHeight = 36


Worksheets("Items").Activate
Set itemrange = Worksheets("Items").Range("a1",
Range("a1").End(xlToRight))
For Each ir In itemrange
If ir.Text = txtRoom Then
Exit For
End If
Next ir
Worksheets("Items").Activate
Select Case txtRoom


Case "Room1"


Set listrange = Worksheets("Items").Range(ir, Range(ir).End(xlDown))
listrange.Copy Destination:=Worksheets("SF 701").Range("a8")


Case "Room2"
....


Case "Room3"
....


Case "Room4"
....


End Select

defining a range
 

Thank you very much. That did the trick.

David

On Mar 17, 6:56*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
ir is a range. The default property is it's value so what you have is the
same as

Set listrange = Worksheets("Items").Range(ir, Range(ir.Value).End(xlDown))
and the value of ir is the contents of the first cell in the range.

try this...

Set listrange = Worksheets("Items").Range(ir, ir.End(xlDown))

if you have decalred ir as a range then you will get the intellisence list
of properties and methods for ir.
Dim ir as Range

--
HTH...

Jim Thomlinson