run time error 91
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 |
run time error 91
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 |
All times are GMT +1. The time now is 04:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com