Can you handle another SORT problem?

[theSquirrel]

I am having a sorting problem, I have number and text data in a
column that is not sorting the way I would like it to. Here is an
example of my data

1+x
1
5
1
1
1
2
4
1
3
3(1)

However then I sort it top to bottom, here is what I get.
1
1
1
1
1
2
3
4
5
1+x
3(1)

Notice the 2 bottom items don't quite make it where I would like them
to (under the 1 and 3 respectively). Here is the chunk of code doing
the sort:

Dim SortRange As Range
Dim SortKey As Range

Set SortRange = TempList.Range("A21:K" & TempList.Cells(17,
12).Value - 1)
Set SortKey = TempList.Range(SortColumnAndRow)

SortRange.Sort Key1:=SortKey, _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

NOTE: SortColumnAndRow is a series of hard coded valid ranges.

I have tried to use xlSortTextAsNumbers as my 'DataOption1' but that
didn't work either.

Is there anything I can do to sort these properly? It is important to
keep in mind that this is column 7 of an 11 column sort.

Any help is appreciated!

 

[Guest]

I would try recording a macro starting from the selection of the range of
cells to the sort order window, and then I would compare the code generated
by Excel with yours.

 

[Dave Peterson]

xlSortTextAsNumbers won't treat 3(1) and 1+x as numbers. It will treat '123
with the apostrophe as a number.

I think you're going to have to separate your keys into another column and then
sort by that column.

 

[theSquirrel]

xlSortTextAsNumbers won't treat 3(1) and 1+x as numbers. It will treat '123
with the apostrophe as a number.

I think you're going to have to separate your keys into another column and then
sort by that column.

bummer, i was afraid of that. I was going to try and avoid that, but
it looks like there is no way around it.

thanks for the answer Dave!

 

[Dave Peterson]

The alternative would be to treat everything as text

00001+x
00001
00003
00003(1)
00005

....

Either way can be pretty ugly.