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? |
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? |
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 |
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? |
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 |
All times are GMT +1. The time now is 06:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com