Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range?
What is wrong with this code?
Dim MyArray As Variant Dim MyRange As Range Dim EndRow As Long Dim EndCol As Integer Debug.Print Sheets("Data").Range("A1") EndRow = Sheets("Data").Range("A1").End(xlDown).Row EndCol = Sheets("Data").Range("A1").End(xlToRight).Column Set MyRange = Worksheets("Data").Range(Cells(1, 1), Cells(EndRow, EndCol)) MyArray = MyRange The Set MyRange line errors out with an Runtime Error. EndRow and EndCol are valid locations, so it has to be how I use the cells in the range statement? Any help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range?
Works for me.
What values do you get for EndRow and EndCol ? What error message do you get ? Regards Trevor "jayklmno" wrote in message ... What is wrong with this code? Dim MyArray As Variant Dim MyRange As Range Dim EndRow As Long Dim EndCol As Integer Debug.Print Sheets("Data").Range("A1") EndRow = Sheets("Data").Range("A1").End(xlDown).Row EndCol = Sheets("Data").Range("A1").End(xlToRight).Column Set MyRange = Worksheets("Data").Range(Cells(1, 1), Cells(EndRow, EndCol)) MyArray = MyRange The Set MyRange line errors out with an Runtime Error. EndRow and EndCol are valid locations, so it has to be how I use the cells in the range statement? Any help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range?
If Data isn't the activesheet, you'll have trouble:
Set MyRange = Worksheets("Data").Range(worksheets("data").Cells( 1, 1), _ worksheets("data").Cells(EndRow, EndCol)) or with worksheets("Data") Set MyRange = .Range(.Cells(1, 1), .Cells(EndRow, EndCol)) end with or even... with worksheets("Data") Set MyRange = .Range("A1", .Cells(EndRow, EndCol)) end with And I like to explicitly use the property: myArray = myRange.value ===== And it turns out that "As Integer" actually slows the pc down. Internally, the pc will convert it to long. Dim EndCol as Long is safer, faster and easier to type <bg jayklmno wrote: What is wrong with this code? Dim MyArray As Variant Dim MyRange As Range Dim EndRow As Long Dim EndCol As Integer Debug.Print Sheets("Data").Range("A1") EndRow = Sheets("Data").Range("A1").End(xlDown).Row EndCol = Sheets("Data").Range("A1").End(xlToRight).Column Set MyRange = Worksheets("Data").Range(Cells(1, 1), Cells(EndRow, EndCol)) MyArray = MyRange The Set MyRange line errors out with an Runtime Error. EndRow and EndCol are valid locations, so it has to be how I use the cells in the range statement? Any help? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range?
Try running it when it isn't the active sheet. My EndRow and EndCol values
are valid, but it is getting an Runtime 1004 - Application-defined or object defined error. This macro is accessing data on another tab in the spreedsheet, not the active tab. "Trevor Shuttleworth" wrote: Works for me. What values do you get for EndRow and EndCol ? What error message do you get ? Regards Trevor "jayklmno" wrote in message ... What is wrong with this code? Dim MyArray As Variant Dim MyRange As Range Dim EndRow As Long Dim EndCol As Integer Debug.Print Sheets("Data").Range("A1") EndRow = Sheets("Data").Range("A1").End(xlDown).Row EndCol = Sheets("Data").Range("A1").End(xlToRight).Column Set MyRange = Worksheets("Data").Range(Cells(1, 1), Cells(EndRow, EndCol)) MyArray = MyRange The Set MyRange line errors out with an Runtime Error. EndRow and EndCol are valid locations, so it has to be how I use the cells in the range statement? Any help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range?
I figured it was something like that. And thanks for the tip about integers.
"Dave Peterson" wrote: If Data isn't the activesheet, you'll have trouble: Set MyRange = Worksheets("Data").Range(worksheets("data").Cells( 1, 1), _ worksheets("data").Cells(EndRow, EndCol)) or with worksheets("Data") Set MyRange = .Range(.Cells(1, 1), .Cells(EndRow, EndCol)) end with or even... with worksheets("Data") Set MyRange = .Range("A1", .Cells(EndRow, EndCol)) end with And I like to explicitly use the property: myArray = myRange.value ===== And it turns out that "As Integer" actually slows the pc down. Internally, the pc will convert it to long. Dim EndCol as Long is safer, faster and easier to type <bg jayklmno wrote: What is wrong with this code? Dim MyArray As Variant Dim MyRange As Range Dim EndRow As Long Dim EndCol As Integer Debug.Print Sheets("Data").Range("A1") EndRow = Sheets("Data").Range("A1").End(xlDown).Row EndCol = Sheets("Data").Range("A1").End(xlToRight).Column Set MyRange = Worksheets("Data").Range(Cells(1, 1), Cells(EndRow, EndCol)) MyArray = MyRange The Set MyRange line errors out with an Runtime Error. EndRow and EndCol are valid locations, so it has to be how I use the cells in the range statement? Any help? -- Dave Peterson |
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 | |||
setting range().hidden=True causes range error 1004 | Excel Programming | |||
Setting a range when value is given | Excel Programming | |||
Need help setting a range | Excel Programming | |||
Setting a Range | Excel Programming |