Sorting help !!!!!!!!!!!!!!!!!

A

Ayo

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
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top