Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
This is what I need:
--------------------------- I am working in worksheet 1.I would like to declare a range calle My_Range in worksheet 2. The cells that make up the range will be known only at run time. ie M range will have n number of rows where n will be determined when th program runs. How do i do this? So far this is what I've done ----------------------------------- Set My_Range = Range(Range("a1"), Range("a1").Offset(n)) This works but it selects the range from the current worksheet i sheet1. When I try this: -------------------- Set My_Range = Range(Worksheets.("sheet2").Range("a1") ksheets.("sheet2").Range("a1").Offset(n)) It gives me an error. Help! I need to finish this asap for an assignment. Pooja -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Hi
try something like the following Dim wks as worksheet dim my_range as range set wks = activeworkbook.worksheets("Sheet2") set my_range=wks.range(cells(1,1),cells(n,1)) -- Regards Frank Kabel Frankfurt, Germany This is what I need: --------------------------- I am working in worksheet 1.I would like to declare a range called My_Range in worksheet 2. The cells that make up the range will be known only at run time. ie My range will have n number of rows where n will be determined when the program runs. How do i do this? So far this is what I've done ----------------------------------- Set My_Range = Range(Range("a1"), Range("a1").Offset(n)) This works but it selects the range from the current worksheet ie sheet1. When I try this: -------------------- Set My_Range = Range(Worksheets.("sheet2").Range("a1"), ksheets.("sheet2").Range("a1").Offset(n)) It gives me an error. Help! I need to finish this asap for an assignment. Pooja. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Set My_Range = Sheets("Sheet2").Range("A1:A" & n)
Or for whole rows: Set My_Range = Sheets("Sheet2").Rows("1:" & n ----- pooja wrote: ---- This is what I need -------------------------- I am working in worksheet 1.I would like to declare a range calle My_Range in worksheet 2. The cells that make up the range will be known only at run time. ie M range will have n number of rows where n will be determined when th program runs How do i do this So far this is what I've don ---------------------------------- Set My_Range = Range(Range("a1"), Range("a1").Offset(n) This works but it selects the range from the current worksheet i sheet1 When I try this ------------------- Set My_Range = Range(Worksheets.("sheet2").Range("a1") ksheets.("sheet2").Range("a1").Offset(n) It gives me an error Help! I need to finish this asap for an assignment Pooja -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
If I understand you correctly:
Dim My_Range As Range Set My_Range = Worksheets("Sheet2").Range("A1").Resize(n, 1) In article , pooja wrote: This is what I need: --------------------------- I am working in worksheet 1.I would like to declare a range called My_Range in worksheet 2. The cells that make up the range will be known only at run time. ie My range will have n number of rows where n will be determined when the program runs. How do i do this? So far this is what I've done ----------------------------------- Set My_Range = Range(Range("a1"), Range("a1").Offset(n)) This works but it selects the range from the current worksheet ie sheet1. When I try this: -------------------- Set My_Range = Range(Worksheets.("sheet2").Range("a1"), ksheets.("sheet2").Range("a1").Offset(n)) It gives me an error. Help! I need to finish this asap for an assignment. Pooja. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
n = 7
Set rng = Sheets(2).Range("A1") Set my_Range = Range(rng(1), rng(n)) Alan Beban pooja < wrote: This is what I need: --------------------------- I am working in worksheet 1.I would like to declare a range called My_Range in worksheet 2. The cells that make up the range will be known only at run time. ie My range will have n number of rows where n will be determined when the program runs. How do i do this? So far this is what I've done ----------------------------------- Set My_Range = Range(Range("a1"), Range("a1").Offset(n)) This works but it selects the range from the current worksheet ie sheet1. When I try this: -------------------- Set My_Range = Range(Worksheets.("sheet2").Range("a1"), ksheets.("sheet2").Range("a1").Offset(n)) It gives me an error. Help! I need to finish this asap for an assignment. Pooja. --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
This will cause a run-time error if Sheet2 is not active.
Cells() defaults to the active worksheet, so this is the equivalient of Dim wks as worksheet dim my_range as range set wks = activeworkbook.worksheets("Sheet2") set my_range=wks.range(ActiveSheet.cells(1,1),ActiveSh eet.cells(n,1)) Instead, try: Dim My_Range As Range With ActiveWorkbook.Worksheets("Sheet2") Set My_Range = .Range(.Cells(1, 1), .Cells(n, 1)) End With or, a bit simpler: Dim My_Range As Range Set My_Range = Worksheets("Sheet2").Range("A1").Resize(n, 1) In article , "Frank Kabel" wrote: Dim wks as worksheet dim my_range as range set wks = activeworkbook.worksheets("Sheet2") set my_range=wks.range(cells(1,1),cells(n,1)) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Hi JE
I always forget the worksheet identifier for the cells object :-( thanks for your correction :-) -- Regards Frank Kabel Frankfurt, Germany JE McGimpsey wrote: This will cause a run-time error if Sheet2 is not active. Cells() defaults to the active worksheet, so this is the equivalient of Dim wks as worksheet dim my_range as range set wks = activeworkbook.worksheets("Sheet2") set my_range=wks.range(ActiveSheet.cells(1,1),ActiveSh eet.cells(n,1)) Instead, try: Dim My_Range As Range With ActiveWorkbook.Worksheets("Sheet2") Set My_Range = .Range(.Cells(1, 1), .Cells(n, 1)) End With or, a bit simpler: Dim My_Range As Range Set My_Range = Worksheets("Sheet2").Range("A1").Resize(n, 1) In article , "Frank Kabel" wrote: Dim wks as worksheet dim my_range as range set wks = activeworkbook.worksheets("Sheet2") set my_range=wks.range(cells(1,1),cells(n,1)) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
You received better answers, but for interest, you second attempt was headed
in the right direction - you just had a syntax error: Sub Tester6() n = 7 Set My_Range = Range(Worksheets("sheet2").Range("a1"), _ Worksheets("sheet2").Range("a1").Offset(n)) MsgBox My_Range.Address(external:=True) End Sub works. -- Regards, Tom Ogilvy "pooja " wrote in message ... This is what I need: --------------------------- I am working in worksheet 1.I would like to declare a range called My_Range in worksheet 2. The cells that make up the range will be known only at run time. ie My range will have n number of rows where n will be determined when the program runs. How do i do this? So far this is what I've done ----------------------------------- Set My_Range = Range(Range("a1"), Range("a1").Offset(n)) This works but it selects the range from the current worksheet ie sheet1. When I try this: -------------------- Set My_Range = Range(Worksheets.("sheet2").Range("a1"), ksheets.("sheet2").Range("a1").Offset(n)) It gives me an error. Help! I need to finish this asap for an assignment. Pooja. --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Frank Kabel wrote:
Hi JE I always forget the worksheet identifier for the cells object :-( That might be a good reason to systematically avoid the "cells object". I.e., set rng = Worksheets("whatever").range("A1") rng then carries its qualification with it. Alan Beban |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
No reason to abandon the Cells property. Just qualify it.
Sub testIt4() Dim aRng As Range, n As Integer n = 7 With Worksheets(2) Set aRng = Range(.Cells(1, 1), .Cells(n, 1)) End With MsgBox aRng.Parent.Name & "," & aRng.Address End Sub or, if using the equivalent of SHIFT+CTRL+down arrow: With Worksheets(2).Cells(1, 1) Set aRng = Range(.Item(1), .End(xlDown)) -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi JE I always forget the worksheet identifier for the cells object :-( thanks for your correction :-) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Only reason I can think of is that
Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1) is faster than the additional reference resolution in With Worksheets(2) Set aRng = .Range(.Cells(1, 1), .Cells(n, 1)) End With In article <MPG.1b1bb58da25e61729897ed@news-server, Tushar Mehta wrote: No reason to abandon the Cells property. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Hi JE,
I was responding to a comment that I mistakenly attributed to Frank ('That might be a good reason to systematically avoid the "cells object".)' Actually, the quote is from Alan's response to Frank. Note, though, that neither are you abandoning the Cells property; just using it once (with a Resize) rather than twice. Somebody at some point posted an ad hoc analysis of how long it took to reference a cell using each of six or seven different methods. But, since I don't usually worry about computer speed, I couldn't give you a reference without some amount of digging. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Only reason I can think of is that Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1) is faster than the additional reference resolution in With Worksheets(2) Set aRng = .Range(.Cells(1, 1), .Cells(n, 1)) End With In article <MPG.1b1bb58da25e61729897ed@news-server, Tushar Mehta wrote: No reason to abandon the Cells property. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Another reason is that
Set bRng = Worksheets(2).Range("A1"), Set aRng = Range(bRng(1,1), bRng(n,1)) is faster as well. And, if looping or multiple uses are required in the application, after Set bRng = Worksheets(2).Range("A1"), Set aRng = Range(bRng(1,1), bRng(n,1)) is faster than Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1) Alan Beban JE McGimpsey wrote: Only reason I can think of is that Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1) is faster than the additional reference resolution in With Worksheets(2) Set aRng = .Range(.Cells(1, 1), .Cells(n, 1)) End With In article <MPG.1b1bb58da25e61729897ed@news-server, Tushar Mehta wrote: No reason to abandon the Cells property. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Range Object
Tushar Mehta wrote:
No reason to abandon the Cells property. Just qualify it. Well, the reason I suggested abandoning it is that the OP said he has difficulty *remembering* to qualify it, so "just qualify it" is not much help. And the fact remains that Set bRng = Worksheets(2).Range("A1") Set aRng = Range(bRng(1,1),bRng(n,1)) is faster than With Worksheets(2) Set aRng = Range(.Cells(1, 1), .Cells(n, 1)) End With After stumbling onto the then undocumented (as far as I could ascertain) direct indexing of a range object variable many years ago, I've never understood why so many people cling to and proliferate the relatively more cumbersome and inefficient Cells property in lieu of it. Alan Beban Sub testIt4() Dim aRng As Range, n As Integer n = 7 With Worksheets(2) Set aRng = Range(.Cells(1, 1), .Cells(n, 1)) End With MsgBox aRng.Parent.Name & "," & aRng.Address End Sub or, if using the equivalent of SHIFT+CTRL+down arrow: With Worksheets(2).Cells(1, 1) Set aRng = Range(.Item(1), .End(xlDown)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Declaring as Object rather than Excel.Worksheet | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |