Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime 1004 error
Here is the whole macro. There is a type mismatch on the first set range
presumably because it is not a range type variable. But if I change the range variant type to range it does the same thing. Thanks, Public Sub CoerceAsNumDeptIDSort() Dim Rng, rng1 As Range Dim cell As Range 'finds the number of the last column ' Sorts by Item Name, Dept, Status# Macro ' sorts on DeptID & StatusID & Item Name With ActiveSheet Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft)).End(xlUp)) 'Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 25).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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime 1004 error
Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1,
Columns.Count).End(xlToLeft)).End(xlUp)) should be Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft).Column).End(xlUp)) to highlight the difference ..Cells(1, Columns.Count).End(xlToLeft) should be .Cells(1, Columns.Count).End(xlToLeft).Column -- Regards, Tom Ogilvy Janis" wrote: Here is the whole macro. There is a type mismatch on the first set range presumably because it is not a range type variable. But if I change the range variant type to range it does the same thing. Thanks, Public Sub CoerceAsNumDeptIDSort() Dim Rng, rng1 As Range Dim cell As Range 'finds the number of the last column ' Sorts by Item Name, Dept, Status# Macro ' sorts on DeptID & StatusID & Item Name With ActiveSheet Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft)).End(xlUp)) 'Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 25).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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime 1004 error
thanks, so it didn't know what object to go left of. Thanks again.
"Tom Ogilvy" wrote: Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft)).End(xlUp)) should be Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft).Column).End(xlUp)) to highlight the difference .Cells(1, Columns.Count).End(xlToLeft) should be .Cells(1, Columns.Count).End(xlToLeft).Column -- Regards, Tom Ogilvy Janis" wrote: Here is the whole macro. There is a type mismatch on the first set range presumably because it is not a range type variable. But if I change the range variant type to range it does the same thing. Thanks, Public Sub CoerceAsNumDeptIDSort() Dim Rng, rng1 As Range Dim cell As Range 'finds the number of the last column ' Sorts by Item Name, Dept, Status# Macro ' sorts on DeptID & StatusID & Item Name With ActiveSheet Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft)).End(xlUp)) 'Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 25).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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime 1004 error
No, as written it tried use the value of the last filled cell on the first
row as the column number. If that cell has contained a number between 1 and 256 or valid column letters, your code might have worked but probably not properly or failed on some other line. Fortunately it failed on the troublesome line. -- Regards, Tom Ogilvy "Janis" wrote: thanks, so it didn't know what object to go left of. Thanks again. "Tom Ogilvy" wrote: Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft)).End(xlUp)) should be Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft).Column).End(xlUp)) to highlight the difference .Cells(1, Columns.Count).End(xlToLeft) should be .Cells(1, Columns.Count).End(xlToLeft).Column -- Regards, Tom Ogilvy Janis" wrote: Here is the whole macro. There is a type mismatch on the first set range presumably because it is not a range type variable. But if I change the range variant type to range it does the same thing. Thanks, Public Sub CoerceAsNumDeptIDSort() Dim Rng, rng1 As Range Dim cell As Range 'finds the number of the last column ' Sorts by Item Name, Dept, Status# Macro ' sorts on DeptID & StatusID & Item Name With ActiveSheet Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .Cells(1, Columns.Count).End(xlToLeft)).End(xlUp)) 'Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 25).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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error '1004' | Excel Discussion (Misc queries) | |||
Keep getting runtime error 1004 | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Runtime error 1004 | Excel Programming | |||
runtime error 1004 | Excel Programming |