Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in sort routines
There is a bug in my very simple sort routines that does not occur
consistently - and therefore baffles me. Perhaps I'm missing something obvious. In a tab containing essentially columns of numbers (percents), I have a label defined as follows: citus=$a$11:$s$147. My intention is to create a bunch of essentially identical sort routines which will sort this citus selection according firstly to one of the columns from L to R in descending order, and then secondly column F also in descending order - ie, the pairs of sort orders are L+F, M+F, N+F, etc. Here is what one such routine looks like: Sub sort_ctius_3m() Application.Goto Reference:="ctius" Selection.Sort Key1:=Range("P11"), Order1:=xlDescending, Key2:=Range( _ "F11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal Range("P9").Select End Sub This routine was originally created via the "record macro" feature and I just copied & pasted numerous of them and changed the SortKey1 and the Range().Select to the columns I needed for each. They all reside in the same "module". The bug is: these sort routines work some times (say initially when the file is opened) and then at some point (and I have no clue when this point will show up) the routines would sort all rows in the citus selection EXCEPT the first row. And then for unknown reasons, after running these sort routines a few more times, they would start to work properly again. If someone can point out to me what I'm doing wrong that would be very much appreciated. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in sort routines
Try this code. I think the problem is either you were on the wrong worksheet
or excel didn't like sorting a named range. This should solve both problems. Sub sort_ctius_3m() Set sht = Range("ctius").Parent SortRange = Range("ctius").Address(external:=True) With sht .Range(SortRange).Sort _ Key1:=.Range("P11"), _ Order1:=xlDescending, _ Key2:=.Range("F11"), _ Order2:=xlDescending, _ Header:=xlNo End With End Sub "rockhammer" wrote: There is a bug in my very simple sort routines that does not occur consistently - and therefore baffles me. Perhaps I'm missing something obvious. In a tab containing essentially columns of numbers (percents), I have a label defined as follows: citus=$a$11:$s$147. My intention is to create a bunch of essentially identical sort routines which will sort this citus selection according firstly to one of the columns from L to R in descending order, and then secondly column F also in descending order - ie, the pairs of sort orders are L+F, M+F, N+F, etc. Here is what one such routine looks like: Sub sort_ctius_3m() Application.Goto Reference:="ctius" Selection.Sort Key1:=Range("P11"), Order1:=xlDescending, Key2:=Range( _ "F11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal Range("P9").Select End Sub This routine was originally created via the "record macro" feature and I just copied & pasted numerous of them and changed the SortKey1 and the Range().Select to the columns I needed for each. They all reside in the same "module". The bug is: these sort routines work some times (say initially when the file is opened) and then at some point (and I have no clue when this point will show up) the routines would sort all rows in the citus selection EXCEPT the first row. And then for unknown reasons, after running these sort routines a few more times, they would start to work properly again. If someone can point out to me what I'm doing wrong that would be very much appreciated. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in sort routines
Hi Joel, thanks for the pointers. The one thing that jumped out immediately
when reading your code is the header setting. Yours is "No" whereas the record macro feature gave me "Guess". Changing Guess into No seems to solve the problem. I guess we don't want excel to guess. Thanks also for showing me a new way to code. I have tried it and your code works too. I have not used the "with" construct before but will utilize that in the future. "Joel" wrote: Try this code. I think the problem is either you were on the wrong worksheet or excel didn't like sorting a named range. This should solve both problems. Sub sort_ctius_3m() Set sht = Range("ctius").Parent SortRange = Range("ctius").Address(external:=True) With sht .Range(SortRange).Sort _ Key1:=.Range("P11"), _ Order1:=xlDescending, _ Key2:=.Range("F11"), _ Order2:=xlDescending, _ Header:=xlNo End With End Sub "rockhammer" wrote: There is a bug in my very simple sort routines that does not occur consistently - and therefore baffles me. Perhaps I'm missing something obvious. In a tab containing essentially columns of numbers (percents), I have a label defined as follows: citus=$a$11:$s$147. My intention is to create a bunch of essentially identical sort routines which will sort this citus selection according firstly to one of the columns from L to R in descending order, and then secondly column F also in descending order - ie, the pairs of sort orders are L+F, M+F, N+F, etc. Here is what one such routine looks like: Sub sort_ctius_3m() Application.Goto Reference:="ctius" Selection.Sort Key1:=Range("P11"), Order1:=xlDescending, Key2:=Range( _ "F11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal Range("P9").Select End Sub This routine was originally created via the "record macro" feature and I just copied & pasted numerous of them and changed the SortKey1 and the Range().Select to the columns I needed for each. They all reside in the same "module". The bug is: these sort routines work some times (say initially when the file is opened) and then at some point (and I have no clue when this point will show up) the routines would sort all rows in the citus selection EXCEPT the first row. And then for unknown reasons, after running these sort routines a few more times, they would start to work properly again. If someone can point out to me what I'm doing wrong that would be very much appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Backup routines | Excel Programming | |||
VBA - sub routines Help please | New Users to Excel | |||
VBA routines - help please | Excel Discussion (Misc queries) | |||
Sorting Routines | Excel Programming | |||
Calling sub routines | Excel Programming |