Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
I am puzzled why the following code stops when it reaches
Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
I'm able to select it just fine. The code works for me. What is description
of the error you are getting? -- Cheers, Ryan "Gotroots" wrote: I am puzzled why the following code stops when it reaches Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
Run-time error '1004':
Select method of Range class failed Would it be anything to do with the fact the workbook is slow to open and the code proceeds prematurely to the next line in the code. The workbook is not particularly large 1.17MB although there are upwards of 100,000 array formulas contained in the workbook. "Ryan H" wrote: I'm able to select it just fine. The code works for me. What is description of the error you are getting? -- Cheers, Ryan "Gotroots" wrote: I am puzzled why the following code stops when it reaches Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
Try using this:
Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" With ActiveWorkbook.Sheets("A") .Activate .Range("B11:Z7800").Select .Sort.SortFields.Clear .Sort.SortFields.Add Key:=Range("B11:B7800"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal .Sort .SetRange Range("B11:Z7800") End With Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Gotroots" wrote: Run-time error '1004': Select method of Range class failed Would it be anything to do with the fact the workbook is slow to open and the code proceeds prematurely to the next line in the code. The workbook is not particularly large 1.17MB although there are upwards of 100,000 array formulas contained in the workbook. "Ryan H" wrote: I'm able to select it just fine. The code works for me. What is description of the error you are getting? -- Cheers, Ryan "Gotroots" wrote: I am puzzled why the following code stops when it reaches Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
Sorry to report but it has stopped at: ..Activate "Ryan H" wrote: Try using this: Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" With ActiveWorkbook.Sheets("A") .Activate .Range("B11:Z7800").Select .Sort.SortFields.Clear .Sort.SortFields.Add Key:=Range("B11:B7800"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal .Sort .SetRange Range("B11:Z7800") End With Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Gotroots" wrote: Run-time error '1004': Select method of Range class failed Would it be anything to do with the fact the workbook is slow to open and the code proceeds prematurely to the next line in the code. The workbook is not particularly large 1.17MB although there are upwards of 100,000 array formulas contained in the workbook. "Ryan H" wrote: I'm able to select it just fine. The code works for me. What is description of the error you are getting? -- Cheers, Ryan "Gotroots" wrote: I am puzzled why the following code stops when it reaches Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
I'd use the old xl2003 syntax (still supported in xl2007):
dim wkbk as workbook dim wks as worksheet set wkbk = Workbooks.Open(Filename:="Z:\Excel docs\Uses\Uses_A.xlsm") set wks = wkbk.worksheets("A") with wks with .range("B11:z7800") .sort key1:=.columns(1), order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom end with end with ============= I guessed that your data had headers in row 11. I see by your code that you're not including column A in the sort range. If you meant that, then ignore this. But if you wanted to include column A and sort by column B, you could use: with wks with .range("a11:z7800") .sort key1:=.columns(2), order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom end with end with Gotroots wrote: I am puzzled why the following code stops when it reaches Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
I am pleased to say your solution was successful.
I will need to tweak it a bit though. Thank you Gotroots "Dave Peterson" wrote: I'd use the old xl2003 syntax (still supported in xl2007): dim wkbk as workbook dim wks as worksheet set wkbk = Workbooks.Open(Filename:="Z:\Excel docs\Uses\Uses_A.xlsm") set wks = wkbk.worksheets("A") with wks with .range("B11:z7800") .sort key1:=.columns(1), order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom end with end with ============= I guessed that your data had headers in row 11. I see by your code that you're not including column A in the sort range. If you meant that, then ignore this. But if you wanted to include column A and sort by column B, you could use: with wks with .range("a11:z7800") .sort key1:=.columns(2), order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom end with end with Gotroots wrote: I am puzzled why the following code stops when it reaches Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. -- Dave Peterson . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort halted
Ryan
I stated .Activate in fact it should have been .Sort Anyway the solution Dave Peterson provided was successful. Thank you Ryan, I appreciate your effort. "Gotroots" wrote: Sorry to report but it has stopped at: .Activate "Ryan H" wrote: Try using this: Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" With ActiveWorkbook.Sheets("A") .Activate .Range("B11:Z7800").Select .Sort.SortFields.Clear .Sort.SortFields.Add Key:=Range("B11:B7800"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal .Sort .SetRange Range("B11:Z7800") End With Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Gotroots" wrote: Run-time error '1004': Select method of Range class failed Would it be anything to do with the fact the workbook is slow to open and the code proceeds prematurely to the next line in the code. The workbook is not particularly large 1.17MB although there are upwards of 100,000 array formulas contained in the workbook. "Ryan H" wrote: I'm able to select it just fine. The code works for me. What is description of the error you are getting? -- Cheers, Ryan "Gotroots" wrote: I am puzzled why the following code stops when it reaches Range("B11:Z7800").Select Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm" Sheets("A").Select Range("B11:Z7800").Select ActiveWorkbook.Worksheets("A").Sort.SortFields.Cle ar ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("B11:B7800" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("A").Sort .SetRange Range("B11:Z7800") Anyone see something not quite right with the code. Thank you for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
Macro in File halted All Visual Basic Settings? | Excel Programming | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
How to obtail a list of all Variables with values in halted proced | Excel Discussion (Misc queries) |