sort doesn't sort numerically 2nd request

[Guest]

I got an answer on this but maybe I missed something. The field values from
Access db are
strings but they are the department ID numbers. Also the statusID is a
number 1...4. Someone helpfully said to coerce it to a number by adding
columns(1)=columns(1) to the macro. I tried this and I also tried
columns(16)= columns(16) becuase that is the first sort key. It still sorts
the numbers as text,
1
1000
2
etc.
What did I do wrong, or how do I coerce the values in fields #16 and 19 to
numbers?

thanks,

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


Columns(16).Value = Columns(16).Value
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

 

[Guest]

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, rng1 as Range, cell 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))
set rng1 = Rng.Columns(16).Cells
set rng1 = rng1.offset(1,0).Resize(rng1.count-1)
for each cell in rng1
cell.numberformat = "General"
cell.Value = clng(cell.value)
Next
set rng1 = rng.columns(19).Cells
set rng1 = rng1.offset(1,0).Resize(rng1.count-1)
for each cell in rng1
cell.numberformat = "General"
cell.Value = clng(cell.value)
Next

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

 

[Guest]

How is your column Formated (as text or numbers). Give this a try...

Columns(16).NumberFormat = "0"
Columns(16).Value = Columns(1).Value

 

[Guest]

Typo...
Columns(16).Value = Columns(16).Value

 

[Mike Fogleman]

You may have to loop through each column that you want text converted to
numbers before you sort by those columns. This will convert columns 16 & 19
to numbers.

Sub Sort()
Dim rng As Range, c As Range

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

Set rng = .Range(.Cells(1, 16), .Cells(Rows.Count, 16).End(xlUp)) 'column 16
MsgBox rng.Address

For Each c In rng
c.Value = Val(c)
Next

Set rng = .Range(.Cells(1, 19), .Cells(Rows.Count, 19).End(xlUp)) 'column 19
MsgBox rng.Address

For Each c In rng
c.Value = Val(c)
Next

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


Mike F