Is there a way to have a macro written to include many sort keys? The
one I have now sorts for 3 things - but it seems when I try to sort
for 4, it gives me an error?

This is what I currently have:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Header:=xlYes

End Sub


I tried just adding in to make it:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Key4:=Range("D1"), Header:=xlYes

End Sub

But it says "Named Argument Not Found"... am I doing something wrong?

Can someone help??

(I didn't write the macro - someone else just told me what to put and
where it went!)

Thanks :)

Just as in XL's Sort command, you can only sort 3 columns at a time, so
sort the 4th column first, then the other three. Try:


Public Sub SortMe()
Cells.Sort Key1:=Range("D1"), Header:=xlYes
Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"), _
Key3:=Range("C1"), Header:=xlYes
End Sub


In article om,
wrote:

Is there a way to have a macro written to include many sort keys? The
one I have now sorts for 3 things - but it seems when I try to sort
for 4, it gives me an error?

This is what I currently have:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Header:=xlYes

End Sub


I tried just adding in to make it:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Key4:=Range("D1"), Header:=xlYes

End Sub

But it says "Named Argument Not Found"... am I doing something wrong?

Can someone help??

(I didn't write the macro - someone else just told me what to put and
where it went!)

Thanks :)

On Aug 1, 12:37 pm, JE McGimpsey wrote:
Just as in XL's Sort command, you can only sort 3 columns at a time, so
sort the 4th column first, then the other three. Try:

Public Sub SortMe()
Cells.Sort Key1:=Range("D1"), Header:=xlYes
Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"), _
Key3:=Range("C1"), Header:=xlYes
End Sub

In article om,



wrote:
Is there a way to have a macro written to include many sort keys? The
one I have now sorts for 3 things - but it seems when I try to sort
for 4, it gives me an error?

This is what I currently have:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Header:=xlYes

End Sub

I tried just adding in to make it:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Key4:=Range("D1"), Header:=xlYes

End Sub

But it says "Named Argument Not Found"... am I doing something wrong?

Can someone help??

(I didn't write the macro - someone else just told me what to put and
where it went!)

Thanks :)- Hide quoted text -

- Show quoted text -

This worked :) Thanks! But... I forgot... I actually need it to sort
not alphabetically but by an order considered "in severity". The
database is listing levels of corrective action for employees... the
levels are verbal, written, final written and then termination.

Is there a way to use this to sort first by the 3 columns I had before
(which, for reference, are the supervisor name, last name of employee,
first name of employee - respectively) and then sort it by the level
of corrective action where verbal is the first one on the list,
written is second, final written is third and termination is last in
order?

Thanks! :)

You should introduce a helper column and allocate a code for the
descriptions of severity. You might like to set up a small table
somewhere (eg X1:Y4) like this:

verbal 7
written 5
final written 3
termination 1

then you could use a VLOOKUP formula to "translate" the description to
its value in the helper column. You would then use the helper column
as one of your sort keys. Note that I've left gaps in the numbers in
case you introduce other descriptions in the future, but you could
just use 1 to 4.

Hope this helps.

Pete


This worked :) Thanks! But... I forgot... I actually need it to sort
not alphabetically but by an order considered "in severity". The
database is listing levels of corrective action for employees... the
levels are verbal, written, final written and then termination.

Is there a way to use this to sort first by the 3 columns I had before
(which, for reference, are the supervisor name, last name of employee,
first name of employee - respectively) and then sort it by the level
of corrective action where verbal is the first one on the list,
written is second, final written is third and termination is last in
order?

Thanks! :)- Hide quoted text -

- Show quoted text -

Actually, I've just re-read your last sentence, and I think you'll
want to change the numbers around so that verbal is 1 etc.

Incidentally, you could save doing the sort twice by joining together
the employee's last name and first name into one column, so that you
only have 3 sort keys.

Hope this helps.

Pete

On Aug 2, 12:12 am, Pete_UK wrote:
You should introduce a helper column and allocate a code for the
descriptions of severity. You might like to set up a small table
somewhere (eg X1:Y4) like this:

verbal 7
written 5
final written 3
termination 1

then you could use a VLOOKUP formula to "translate" the description to
its value in the helper column. You would then use the helper column
as one of your sort keys. Note that I've left gaps in the numbers in
case you introduce other descriptions in the future, but you could
just use 1 to 4.

Hope this helps.

Pete





This worked :) Thanks! But... I forgot... I actually need it to sort
not alphabetically but by an order considered "in severity". The
database is listing levels of corrective action for employees... the
levels are verbal, written, final written and then termination.

Is there a way to use this to sort first by the 3 columns I had before
(which, for reference, are the supervisor name, last name of employee,
first name of employee - respectively) and then sort it by the level
of corrective action where verbal is the first one on the list,
written is second, final written is third and termination is last in
order?

Thanks! :)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

One way:

Public Sub SortMe()
Dim nListCount As Long
With Application
.AddCustomList ListArray:=Array( _
"verbal", "written", "final written", "termination")
nListCount = .CustomListCount
End With
Cells.Sort Key1:=Range("D1"), _
Header:=xlYes, _
OrderCustom:=nListCount
Cells.Sort Key1:=Range("F1"), _
Key2:=Range("B1"), _
Key3:=Range("C1"), _
Header:=xlYes
Application.DeleteCustomList nListCount
End Sub


In article .com,
wrote:

Is there a way to use this to sort first by the 3 columns I had before
(which, for reference, are the supervisor name, last name of employee,
first name of employee - respectively) and then sort it by the level
of corrective action where verbal is the first one on the list,
written is second, final written is third and termination is last in
order?

On Aug 2, 12:10 am, JE McGimpsey wrote:
One way:

Public Sub SortMe()
Dim nListCount As Long
With Application
.AddCustomList ListArray:=Array( _
"verbal", "written", "final written", "termination")
nListCount = .CustomListCount
End With
Cells.Sort Key1:=Range("D1"), _
Header:=xlYes, _
OrderCustom:=nListCount
Cells.Sort Key1:=Range("F1"), _
Key2:=Range("B1"), _
Key3:=Range("C1"), _
Header:=xlYes
Application.DeleteCustomList nListCount
End Sub

In article .com,



wrote:
Is there a way to use this to sort first by the 3 columns I had before
(which, for reference, are the supervisor name, last name of employee,
first name of employee - respectively) and then sort it by the level
of corrective action where verbal is the first one on the list,
written is second, final written is third and termination is last in
order?- Hide quoted text -

- Show quoted text -

I don't know if I'll get any more assistance with this now that so
much time has passed. I got busy yesterday and forgot all about
this...

I tried the macro that was written - it didn't work. Not sure if I
did something incorrect. I put it where I had the previous macro...

As for the table option - can I put the table on a separate sheet with
the other info for the drop down boxes?

The reason I am separating first and last name is that if I leave it
as one box, I imagine people will inevitably do it incorrectly by
putting first name, last name while others put last name, first
name... there's no way to ask last name then first name within one
cell is there?

You could put the table on a separate sheet. However, as you only have
four items you can build these into the formula like so:

=LOOKUP(D2,{"final written","termination","verbal","written"},
{3,4,1,2})

I am assuming that you have these values in column D - just copy the
formula down your helper column and then sort on this column.

You could put a comment in each cell (or use Data Validation - input
message) to tell your Users about Last Name first, but what I was
suggesting is to leave your columns as they are and to use another
helper column (which could be hidden from view) - in this column you
can just join the Last Name with the First Name using &, and then have
this as one sort field.

If you don't join them then I would suggest that you do a sort using
Last Name and First Name columns as the sort keys first, and then do a
sort on column F and the helper column for actions.

Hope this helps.

Pete


On Aug 2, 1:15 pm, wrote:

As for the table option - can I put the table on a separate sheet with
the other info for the drop down boxes?

The reason I am separating first and last name is that if I leave it
as one box, I imagine people will inevitably do it incorrectly by
putting first name, last name while others put last name, first
name... there's no way to ask last name then first name within one
cell is there?- Hide quoted text -

- Show quoted text -

On Aug 2, 12:10 am, JE McGimpsey wrote:
One way:

Public Sub SortMe()
Dim nListCount As Long
With Application
.AddCustomList ListArray:=Array( _
"verbal", "written", "final written", "termination")
nListCount = .CustomListCount
End With
Cells.Sort Key1:=Range("D1"), _
Header:=xlYes, _
OrderCustom:=nListCount
Cells.Sort Key1:=Range("F1"), _
Key2:=Range("B1"), _
Key3:=Range("C1"), _
Header:=xlYes
Application.DeleteCustomList nListCount
End Sub

In article .com,



wrote:
Is there a way to use this to sort first by the 3 columns I had before
(which, for reference, are the supervisor name, last name of employee,
first name of employee - respectively) and then sort it by the level
of corrective action where verbal is the first one on the list,
written is second, final written is third and termination is last in
order?- Hide quoted text -

- Show quoted text -

Oh... also - I think I may have miscommunicated how I wanted it
sorted...

I want it to first sort by column F... after that, I want the D column
(which has the level of correct action) to put all the verbals
together, then written, final written and finally termination... after
those are sorted... I'd like them to be sorted by last name, first
name WITHIN that category..

Does that make sense?

Thanks!