Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to sort automatically in a custom order
I have a list of data where in the very last column of data (Q), users input
a letter for a day of the week they establish. M for Monday, T for tuesday, R for thursday, etc. What I would like to do is generate an excel button that will automatically select the data I want to sort, and then sort it automatically without them having to go to the toolbar and select data filter...etc. If possible I would like to put the days in order, Monday thru Friday, but if not, alphabetical would work. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to sort automatically in a custom order
Here's a quick (and dirty!) solution. Type the following formula into R2
=IF(Q2="M",1,IF(Q2="T",2,IF(Q2="W",3,IF(Q2="R",4,I F(Q2="F",5,IF(Q2="S",6,IF(Q2="Y",7,""))))))) Then copy it way down beyond the data and hide the column. The following macro will sort on column R (I've included two alternatives, the first assumes your table has no headings, the second assumes it does): Sub mySort() Range("A1").CurrentRegion.Sort Key1:=Range("R1"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' Range("A1").CurrentRegion.Sort Key1:=Range("R2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Then create a button on the sheet (from Forms toolbar) or in a toolbar and attach this macro. "Josh Johansen" wrote: I have a list of data where in the very last column of data (Q), users input a letter for a day of the week they establish. M for Monday, T for tuesday, R for thursday, etc. What I would like to do is generate an excel button that will automatically select the data I want to sort, and then sort it automatically without them having to go to the toolbar and select data filter...etc. If possible I would like to put the days in order, Monday thru Friday, but if not, alphabetical would work. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to sort automatically in a custom order
I got to the point where I attempted using the VBA, the if statements were
easy and obsvious, but I think because I am trying to sort a pivot table I am having issues. I should have mentioned that in my original question, but the information is in a pivot table and I am trying to sort the data to a column to the right of the pivot table. Is this even possible? "Smallweed" wrote: Here's a quick (and dirty!) solution. Type the following formula into R2: =IF(Q2="M",1,IF(Q2="T",2,IF(Q2="W",3,IF(Q2="R",4,I F(Q2="F",5,IF(Q2="S",6,IF(Q2="Y",7,""))))))) Then copy it way down beyond the data and hide the column. The following macro will sort on column R (I've included two alternatives, the first assumes your table has no headings, the second assumes it does): Sub mySort() Range("A1").CurrentRegion.Sort Key1:=Range("R1"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' Range("A1").CurrentRegion.Sort Key1:=Range("R2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Then create a button on the sheet (from Forms toolbar) or in a toolbar and attach this macro. "Josh Johansen" wrote: I have a list of data where in the very last column of data (Q), users input a letter for a day of the week they establish. M for Monday, T for tuesday, R for thursday, etc. What I would like to do is generate an excel button that will automatically select the data I want to sort, and then sort it automatically without them having to go to the toolbar and select data filter...etc. If possible I would like to put the days in order, Monday thru Friday, but if not, alphabetical would work. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Sort Order - Wildcard? | Excel Worksheet Functions | |||
Custom Sort order issue | Excel Discussion (Misc queries) | |||
Add sort order to custom header? | Excel Discussion (Misc queries) | |||
Macro with custom sort order | Excel Worksheet Functions | |||
Custom Sort Order | Excel Discussion (Misc queries) |