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.
|
Similar Threads
