I get a type mismatch on Clng on the first for each. Was I supposed to make
the cell variable something other than a range? Thanks,

Private Sub Sort()
Dim rng, rng1, cell As Range
'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


' 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

CLng if a function which coerces a value to a long integer. If your value is
text such as "ABC" then the function will fail as it can not make that into a
long integer...

Also note that your dim statement
Dim rng, rng1, cell As Range
is probably not doing what you think it is. rng and rng1 are both of type
Variant while cell is of type range. Check out this link...
http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Janis" wrote:

I get a type mismatch on Clng on the first for each. Was I supposed to make
the cell variable something other than a range? Thanks,

Private Sub Sort()
Dim rng, rng1, cell As Range
'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


' 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