Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining Range Name | Excel Discussion (Misc queries) | |||
Defining a range in vba???? Please help | Excel Programming | |||
Defining series range for named range | Excel Programming | |||
Defining a range | Excel Programming | |||
Defining a range and use it's value | Excel Programming |