sort doesn't sort numerically
 

This sorts funny. On the first key, which is department number, it starts at
12, instead of one. It goes to 1000, then after 1000, it starts at 12, then
descends to 1. It is almost like a second sort stops half way through and
interupts the first key or it is sorting on Text instead of a number.
i.e,
12
12
13
14
14
14
14
15
15
16
16
16
16.....
1000
12
12
11
11
11
11
10
9
9
9
8
8
7
.....
1

Why doesn't it keep going in numerical order?
-----my macro-----
Sub Sort()
'finds the number of the last column
'Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .cells(1,
columns.count).end(xlToLeft)).End(xlUp))

'
' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet

Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 26).End(xlUp))
MsgBox rng.Address
rng.Sort key1:=.Cells(1, 16), Order1:=xlAscending, _
key2:=.Cells(1, 19), Order2:=xlAscending, _
key3:=.Cells(1, 3), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom
End With


End Sub
-----
just to see the difference I did a recorded macro but you aren't supposed to
use selections but of course the recorded macro works and mine doesn't.
-------recorded macro-----
Sub Macro1()
'
' Macro1
'
' Keyboard Shortcut: Option+Cmd+z
'
Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Key2:=Range("S2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub



Thanks,

sort doesn't sort numerically
 

The data you have is text not numbers. You need to convert the text to
numbers...

Columns(1).Value = Columns(1).Value
--
HTH...

Jim Thomlinson


"Janis" wrote:

This sorts funny. On the first key, which is department number, it starts at
12, instead of one. It goes to 1000, then after 1000, it starts at 12, then
descends to 1. It is almost like a second sort stops half way through and
interupts the first key or it is sorting on Text instead of a number.
i.e,
12
12
13
14
14
14
14
15
15
16
16
16
16.....
1000
12
12
11
11
11
11
10
9
9
9
8
8
7
....
1

Why doesn't it keep going in numerical order?
-----my macro-----
Sub Sort()
'finds the number of the last column
'Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .cells(1,
columns.count).end(xlToLeft)).End(xlUp))

'
' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet

Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 26).End(xlUp))
MsgBox rng.Address
rng.Sort key1:=.Cells(1, 16), Order1:=xlAscending, _
key2:=.Cells(1, 19), Order2:=xlAscending, _
key3:=.Cells(1, 3), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom
End With


End Sub
-----
just to see the difference I did a recorded macro but you aren't supposed to
use selections but of course the recorded macro works and mine doesn't.
-------recorded macro-----
Sub Macro1()
'
' Macro1
'
' Keyboard Shortcut: Option+Cmd+z
'
Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Key2:=Range("S2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub



Thanks,

sort doesn't sort numerically
 

all I can say is thanks,

"Jim Thomlinson" wrote:

The data you have is text not numbers. You need to convert the text to
numbers...

Columns(1).Value = Columns(1).Value
--
HTH...

Jim Thomlinson


"Janis" wrote:

This sorts funny. On the first key, which is department number, it starts at
12, instead of one. It goes to 1000, then after 1000, it starts at 12, then
descends to 1. It is almost like a second sort stops half way through and
interupts the first key or it is sorting on Text instead of a number.
i.e,
12
12
13
14
14
14
14
15
15
16
16
16
16.....
1000
12
12
11
11
11
11
10
9
9
9
8
8
7
....
1

Why doesn't it keep going in numerical order?
-----my macro-----
Sub Sort()
'finds the number of the last column
'Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .cells(1,
columns.count).end(xlToLeft)).End(xlUp))

'
' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet

Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 26).End(xlUp))
MsgBox rng.Address
rng.Sort key1:=.Cells(1, 16), Order1:=xlAscending, _
key2:=.Cells(1, 19), Order2:=xlAscending, _
key3:=.Cells(1, 3), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom
End With


End Sub
-----
just to see the difference I did a recorded macro but you aren't supposed to
use selections but of course the recorded macro works and mine doesn't.
-------recorded macro-----
Sub Macro1()
'
' Macro1
'
' Keyboard Shortcut: Option+Cmd+z
'
Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Key2:=Range("S2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub



Thanks,