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

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

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

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