Prev: See if cell is in Range
Next: Converting a “Delete Column” Macro to a “Delete Row” Macro
From: Ayo on 13 May 2010 15:03
I keep getting a "Application-defined or object-defined error" with the code
below.

I am tring to sort by columns B, C, D and I. Row(4) has the Header for the
data range. I am using Excel 2007 but people with Excel2003 need to be able
to use it too hence this sorting code.
What am I doing wrong here? Help!!!!!!!!!!

With ActiveWorkbook.Worksheets("Report 1").Range("B4:AX" & BOReport_lastRow)
.Cells.Sort _
key1:=Columns(2), order1:=xlAscending, _
key2:=Columns(3), order2:=xlAscending, _
key3:=Columns(4), order3:=xlAscending, _
key4:=Columns(9), order4:=xlAscending, _
Header:=xlYes
End With
From: Dave Peterson on 13 May 2010 16:07
Those columns() are unqualified ranges.

If the code is in a General module, then the columns() belong to the activesheet.

If the code is behind a worksheet module, then the columns() belong to the
worksheet owning the code.

I'd use:

With ActiveWorkbook.Worksheets("Report 1").Range("B4:AX" & BOReport_lastRow)
.Cells.Sort _
key1:=.Columns(2), order1:=xlAscending, _
key2:=.Columns(3), order2:=xlAscending, _
key3:=.Columns(4), order3:=xlAscending, _
key4:=.Columns(9), order4:=xlAscending, _
Header:=xlYes
End With


With ActiveWorkbook.Worksheets("Report 1").Range("B4:AX" & BOReport_lastRow)
.Cells.Sort _
key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
key3:=.Columns(3), order3:=xlAscending, _
key4:=.Columns(8), order4:=xlAscending, _
Header:=xlYes
End With

..columns(1) refers to the object in the previous with statement. In this case,
the range B4:AX###.

And the first column of that range is B. If the column you want as your primary
key is C, then key1:=.columns(2)

ps. In xl2003 and below, you can't have 4 keys. But you can sort twice.

Just use the 4th key as the primary key in the first sort statement and the 1st,
2nd, 3rd key in the second sort statement.




Ayo wrote:

> I keep getting a "Application-defined or object-defined error" with the code
> below.
>
> I am tring to sort by columns B, C, D and I. Row(4) has the Header for the
> data range. I am using Excel 2007 but people with Excel2003 need to be able
> to use it too hence this sorting code.
> What am I doing wrong here? Help!!!!!!!!!!
>
> With ActiveWorkbook.Worksheets("Report 1").Range("B4:AX" & BOReport_lastRow)
> .Cells.Sort _
> key1:=Columns(2), order1:=xlAscending, _
> key2:=Columns(3), order2:=xlAscending, _
> key3:=Columns(4), order3:=xlAscending, _
> key4:=Columns(9), order4:=xlAscending, _
> Header:=xlYes
> End With

--

Dave Peterson
 | 
Pages: 1
Prev: See if cell is in Range
Next: Converting a “Delete Column” Macro to a “Delete Row” Macro