sort doesn't sort numerically 2nd request
 

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

sort doesn't sort numerically 2nd request
 

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

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

--
HTH...

Jim Thomlinson


"Janis" wrote:

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

sort doesn't sort numerically 2nd request
 

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

--
Regards,
Tom Ogilvy


"Janis" wrote:

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

sort doesn't sort numerically 2nd request
 

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

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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

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

--
HTH...

Jim Thomlinson


"Janis" wrote:

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

sort doesn't sort numerically 2nd request
 

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

"Janis" wrote in message
...
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