I'm having trouble with the sort method, but no clue as to why. Here is my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just want
to sort the range I am going to use to populate a listbox. If I select the
range then sort the selection it seems to work, but I didn't think I had to
select a range to be able to use the sort method. Any help?

Ben,

Excel likes to be told exactly what to do, and the parameter syntax needs to
be precise: you were missing the Order1 parameter, and your need a colon
after the key1, along these lines:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:=Sheets("Sheet1").Range("B1"), Order1:=xlAscending
End With

HTH,
Bernie
MS Excel MVP


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why. Here is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just
want
to sort the range I am going to use to populate a listbox. If I select
the
range then sort the selection it seems to work, but I didn't think I had
to
select a range to be able to use the sort method. Any help?

You have a syntax error

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:= Sheets("Sheet1").Range("B1")
End With

--
Regards,
Tom Ogilvy


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why. Here is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just
want
to sort the range I am going to use to populate a listbox. If I select
the
range then sort the selection it seems to work, but I didn't think I had
to
select a range to be able to use the sort method. Any help?

Range("A1:B23").Select

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

this has worked for me - however, i wasn't using the 'with'

don't know if this will work for your situation or not

J

"benb" wrote:

I'm having trouble with the sort method, but no clue as to why. Here is my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just want
to sort the range I am going to use to populate a listbox. If I select the
range then sort the selection it seems to work, but I didn't think I had to
select a range to be able to use the sort method. Any help?

Excel isn't quite that demanding: (from help on Sort)

Order1 Optional XlSortOrder. The sort order for the field or range
specified in Key1.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key1 in descending order.
xlAscending ***default***. Sorts Key1 in ascending order.

Emphasis (***) added.

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...

Ben,

Excel likes to be told exactly what to do, and the parameter syntax needs
to
be precise: you were missing the Order1 parameter, and your need a colon
after the key1, along these lines:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:=Sheets("Sheet1").Range("B1"), Order1:=xlAscending
End With

HTH,
Bernie
MS Excel MVP


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why. Here is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just
want
to sort the range I am going to use to populate a listbox. If I select
the
range then sort the selection it seems to work, but I didn't think I had
to
select a range to be able to use the sort method. Any help?

And just to add to my shame, <g , Help on SORT further states:

-----------------------------
Remarks
The settings for Header, Order1, Order2, Order3, OrderCustom, and
Orientation are saved, for the particular worksheet, each time you use this
method. If you don't specify values for these arguments the next time you
call the method, the saved values are used. Set these arguments explicitly
each time you use Sort method, if you choose not to use the saved values.

-----------------------------



Nonetheless, and while your advice if very sound, the cause of the users'
code failure is not the omission of Order:=xlAscending

but the failure to use the colon equal combination with Key1 as we both
pointed out



--

Regards,

Tom Ogilvy



"Tom Ogilvy" wrote in message
...
You got me there Bernie. <hanging head in shame <g

--
Regards,
Tom Ogilvy


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Tom,

Normally, I would agree, but I have no faith in Excel Help's
truthfulness,
especially due to the persistence of user selected parameters.

If you sort a range descending once, and go to re-sort that range, Excel
will remember and by default choose descending. And the same is true
with
code: Yes, that parameter is optional, but if you want to sort
ascending,
and the range had been sorted descending sometime previously, then you
had
better use the Order parameter, 'default' or not, because Excel won't
default to ascending.

I probably should have included all the other parameters, since they
also
have the same drawbacks, but I couldn't remember their names off the top
of
my head. I know that I've also had problems with headers:, and
orientation(?) - time to break out the macro recorder..... :-)

Bernie


"Tom Ogilvy" wrote in message
...
Excel isn't quite that demanding: (from help on Sort)

Order1 Optional XlSortOrder. The sort order for the field or range
specified in Key1.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key1 in descending order.
xlAscending ***default***. Sorts Key1 in ascending order.

Emphasis (***) added.

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...

Ben,

Excel likes to be told exactly what to do, and the parameter syntax
needs
to
be precise: you were missing the Order1 parameter, and your need a
colon
after the key1, along these lines:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:=Sheets("Sheet1").Range("B1"), Order1:=xlAscending
End With

HTH,
Bernie
MS Excel MVP


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why.
Here
is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I
just
want
to sort the range I am going to use to populate a listbox. If I
select
the
range then sort the selection it seems to work, but I didn't think
I
had
to
select a range to be able to use the sort method. Any help?

Are you sure there aren't any further footnotes concerning phases of the
moon? ;-)

Bernie

"Tom Ogilvy" wrote in message
...
And just to add to my shame, <g , Help on SORT further states:

-----------------------------
Remarks
The settings for Header, Order1, Order2, Order3, OrderCustom, and
Orientation are saved, for the particular worksheet, each time you use
this
method. If you don't specify values for these arguments the next time you
call the method, the saved values are used. Set these arguments explicitly
each time you use Sort method, if you choose not to use the saved values.

-----------------------------



Nonetheless, and while your advice if very sound, the cause of the users'
code failure is not the omission of Order:=xlAscending

but the failure to use the colon equal combination with Key1 as we both
pointed out



--

Regards,

Tom Ogilvy



"Tom Ogilvy" wrote in message
...
You got me there Bernie. <hanging head in shame <g

--
Regards,
Tom Ogilvy


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Tom,

Normally, I would agree, but I have no faith in Excel Help's
truthfulness,
especially due to the persistence of user selected parameters.

If you sort a range descending once, and go to re-sort that range,
Excel
will remember and by default choose descending. And the same is true
with
code: Yes, that parameter is optional, but if you want to sort
ascending,
and the range had been sorted descending sometime previously, then you
had
better use the Order parameter, 'default' or not, because Excel won't
default to ascending.

I probably should have included all the other parameters, since they
also
have the same drawbacks, but I couldn't remember their names off the
top
of
my head. I know that I've also had problems with headers:, and
orientation(?) - time to break out the macro recorder..... :-)

Bernie


"Tom Ogilvy" wrote in message
...
Excel isn't quite that demanding: (from help on Sort)

Order1 Optional XlSortOrder. The sort order for the field or range
specified in Key1.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key1 in descending order.
xlAscending ***default***. Sorts Key1 in ascending order.

Emphasis (***) added.

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...

Ben,

Excel likes to be told exactly what to do, and the parameter
syntax
needs
to
be precise: you were missing the Order1 parameter, and your need a
colon
after the key1, along these lines:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:=Sheets("Sheet1").Range("B1"), Order1:=xlAscending
End With

HTH,
Bernie
MS Excel MVP


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why.
Here
is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but
I'm
continually getting a Sort Method of Range Class Failed error.
I
just
want
to sort the range I am going to use to populate a listbox. If I
select
the
range then sort the selection it seems to work, but I didn't
think
I
had
to
select a range to be able to use the sort method. Any help?