Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sort xlAscedending / xlDescending
Dear All
I have a simple table (with a header row) containing 5 columns - 3 of the columns will need to be sorted by the user. Above each of the columns that need sorting there is a shape that is used to run the sorting macro. Here's what I'd like to do. 1 - Rather than creating 3 macros to sort 3 columns I'd like to create one that runs depending on which button, (or shape in this case), is selected. 2 - Depending on the current order of the sorted list, I'd like the macro to do the opposite - i.e. if the list is currently xlAscending, next time the button is pressed for that list, it sorts in xlDescending. Code below - any help appreciated Trevor Williams. -------- Sub SortEachCol() Dim shp As Shape Set shp = ActiveSheet.Shapes(Application.Caller) myCol = shp.TopLeftCell.Column 'this returns a column number, not letter 'this checks to see last sort order and sets myOrder to the opposite order If Range("L13") = "xlAscending" Then myOrder = "xlDescending" Else myOrder = "xlAscending" End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 'this updates the new sort order Range("L13") = myOrder End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sort xlAscedending / xlDescending
Give this a try...
Sub SortEachCol() Dim shp As Shape Dim lngSortOrder As Long Dim mycol As Long Set shp = ActiveSheet.Shapes(Application.Caller) mycol = shp.TopLeftCell.Column If Cells(13, mycol).Value Cells(14, mycol).Value Then lngSortOrder = xlAscending Else lngSortOrder = xlDescending End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Cells(12, mycol), Order1:=lngSortOrder, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End Sub -- HTH... Jim Thomlinson "Trevor Williams" wrote: Dear All I have a simple table (with a header row) containing 5 columns - 3 of the columns will need to be sorted by the user. Above each of the columns that need sorting there is a shape that is used to run the sorting macro. Here's what I'd like to do. 1 - Rather than creating 3 macros to sort 3 columns I'd like to create one that runs depending on which button, (or shape in this case), is selected. 2 - Depending on the current order of the sorted list, I'd like the macro to do the opposite - i.e. if the list is currently xlAscending, next time the button is pressed for that list, it sorts in xlDescending. Code below - any help appreciated Trevor Williams. -------- Sub SortEachCol() Dim shp As Shape Set shp = ActiveSheet.Shapes(Application.Caller) myCol = shp.TopLeftCell.Column 'this returns a column number, not letter 'this checks to see last sort order and sets myOrder to the opposite order If Range("L13") = "xlAscending" Then myOrder = "xlDescending" Else myOrder = "xlAscending" End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 'this updates the new sort order Range("L13") = myOrder End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sort xlAscedending / xlDescending
You may want to look at this:
http://contextures.com/xlSort02.html From Debra Dalgleish's site. Trevor Williams wrote: Dear All I have a simple table (with a header row) containing 5 columns - 3 of the columns will need to be sorted by the user. Above each of the columns that need sorting there is a shape that is used to run the sorting macro. Here's what I'd like to do. 1 - Rather than creating 3 macros to sort 3 columns I'd like to create one that runs depending on which button, (or shape in this case), is selected. 2 - Depending on the current order of the sorted list, I'd like the macro to do the opposite - i.e. if the list is currently xlAscending, next time the button is pressed for that list, it sorts in xlDescending. Code below - any help appreciated Trevor Williams. -------- Sub SortEachCol() Dim shp As Shape Set shp = ActiveSheet.Shapes(Application.Caller) myCol = shp.TopLeftCell.Column 'this returns a column number, not letter 'this checks to see last sort order and sets myOrder to the opposite order If Range("L13") = "xlAscending" Then myOrder = "xlDescending" Else myOrder = "xlAscending" End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 'this updates the new sort order Range("L13") = myOrder End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sort xlAscedending / xlDescending
Hi Jim
Thanks for the response. I was nearly there with my 'dabblings'. I reverted back to using my range value checking to see if it should be Ascending or Descending as it's not just numbers in the lists, and your code tests to see if cell 1 is < than cell 2... Thanks again. Trevor "Jim Thomlinson" wrote: Give this a try... Sub SortEachCol() Dim shp As Shape Dim lngSortOrder As Long Dim mycol As Long Set shp = ActiveSheet.Shapes(Application.Caller) mycol = shp.TopLeftCell.Column If Cells(13, mycol).Value Cells(14, mycol).Value Then lngSortOrder = xlAscending Else lngSortOrder = xlDescending End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Cells(12, mycol), Order1:=lngSortOrder, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End Sub -- HTH... Jim Thomlinson "Trevor Williams" wrote: Dear All I have a simple table (with a header row) containing 5 columns - 3 of the columns will need to be sorted by the user. Above each of the columns that need sorting there is a shape that is used to run the sorting macro. Here's what I'd like to do. 1 - Rather than creating 3 macros to sort 3 columns I'd like to create one that runs depending on which button, (or shape in this case), is selected. 2 - Depending on the current order of the sorted list, I'd like the macro to do the opposite - i.e. if the list is currently xlAscending, next time the button is pressed for that list, it sorts in xlDescending. Code below - any help appreciated Trevor Williams. -------- Sub SortEachCol() Dim shp As Shape Set shp = ActiveSheet.Shapes(Application.Caller) myCol = shp.TopLeftCell.Column 'this returns a column number, not letter 'this checks to see last sort order and sets myOrder to the opposite order If Range("L13") = "xlAscending" Then myOrder = "xlDescending" Else myOrder = "xlAscending" End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 'this updates the new sort order Range("L13") = myOrder End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sort xlAscedending / xlDescending
Hi Dave. I've downloaded the file from Debra's site and will check it out.
I like the idea of the invisible rectangles! Trevor "Dave Peterson" wrote: You may want to look at this: http://contextures.com/xlSort02.html From Debra Dalgleish's site. Trevor Williams wrote: Dear All I have a simple table (with a header row) containing 5 columns - 3 of the columns will need to be sorted by the user. Above each of the columns that need sorting there is a shape that is used to run the sorting macro. Here's what I'd like to do. 1 - Rather than creating 3 macros to sort 3 columns I'd like to create one that runs depending on which button, (or shape in this case), is selected. 2 - Depending on the current order of the sorted list, I'd like the macro to do the opposite - i.e. if the list is currently xlAscending, next time the button is pressed for that list, it sorts in xlDescending. Code below - any help appreciated Trevor Williams. -------- Sub SortEachCol() Dim shp As Shape Set shp = ActiveSheet.Shapes(Application.Caller) myCol = shp.TopLeftCell.Column 'this returns a column number, not letter 'this checks to see last sort order and sets myOrder to the opposite order If Range("L13") = "xlAscending" Then myOrder = "xlDescending" Else myOrder = "xlAscending" End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 'this updates the new sort order Range("L13") = myOrder End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sort xlAscedending / xlDescending
FYI the code I posted does not rely on the sort values to be numbers. It
works equally well with text. -- HTH... Jim Thomlinson "Trevor Williams" wrote: Hi Jim Thanks for the response. I was nearly there with my 'dabblings'. I reverted back to using my range value checking to see if it should be Ascending or Descending as it's not just numbers in the lists, and your code tests to see if cell 1 is < than cell 2... Thanks again. Trevor "Jim Thomlinson" wrote: Give this a try... Sub SortEachCol() Dim shp As Shape Dim lngSortOrder As Long Dim mycol As Long Set shp = ActiveSheet.Shapes(Application.Caller) mycol = shp.TopLeftCell.Column If Cells(13, mycol).Value Cells(14, mycol).Value Then lngSortOrder = xlAscending Else lngSortOrder = xlDescending End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Cells(12, mycol), Order1:=lngSortOrder, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End Sub -- HTH... Jim Thomlinson "Trevor Williams" wrote: Dear All I have a simple table (with a header row) containing 5 columns - 3 of the columns will need to be sorted by the user. Above each of the columns that need sorting there is a shape that is used to run the sorting macro. Here's what I'd like to do. 1 - Rather than creating 3 macros to sort 3 columns I'd like to create one that runs depending on which button, (or shape in this case), is selected. 2 - Depending on the current order of the sorted list, I'd like the macro to do the opposite - i.e. if the list is currently xlAscending, next time the button is pressed for that list, it sorts in xlDescending. Code below - any help appreciated Trevor Williams. -------- Sub SortEachCol() Dim shp As Shape Set shp = ActiveSheet.Shapes(Application.Caller) myCol = shp.TopLeftCell.Column 'this returns a column number, not letter 'this checks to see last sort order and sets myOrder to the opposite order If Range("L13") = "xlAscending" Then myOrder = "xlDescending" Else myOrder = "xlAscending" End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 'this updates the new sort order Range("L13") = myOrder End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sort xlAscedending / xlDescending
Hi Jim
I have a list of Y / N responses in one column and a list on Months (non-abbreviated e.g. January) in another column. I re-tried your code and it works a couple of times then stops. Can you test it using the above scenario and let me know if it works everytime for you? I'd like to use your version as it's so much neater than mine! Also, another question regarding my list on Months. I've set up validation for the user to choose January - December. I need to sort it from January to December but using the Sort function lists it alphabetically (April-September). To get around it I have code that works out the number of the month in another column and sorts that column instead. (the month column is set as 'general', not 'date') My code is a bit cumbersome, and wondered if you have a better solution? For Each cell In rng If cell = "January" Then cell.Offset(0, -1) = 1 ElseIf cell = "February" Then cell.Offset(0, -1) = 2 ElseIf cell = "March" Then cell.Offset(0, -1) = 3 '...etc Else cell.Offset(0, -1) = "" End If Next "Jim Thomlinson" wrote: FYI the code I posted does not rely on the sort values to be numbers. It works equally well with text. -- HTH... Jim Thomlinson "Trevor Williams" wrote: Hi Jim Thanks for the response. I was nearly there with my 'dabblings'. I reverted back to using my range value checking to see if it should be Ascending or Descending as it's not just numbers in the lists, and your code tests to see if cell 1 is < than cell 2... Thanks again. Trevor "Jim Thomlinson" wrote: Give this a try... Sub SortEachCol() Dim shp As Shape Dim lngSortOrder As Long Dim mycol As Long Set shp = ActiveSheet.Shapes(Application.Caller) mycol = shp.TopLeftCell.Column If Cells(13, mycol).Value Cells(14, mycol).Value Then lngSortOrder = xlAscending Else lngSortOrder = xlDescending End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Cells(12, mycol), Order1:=lngSortOrder, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End Sub -- HTH... Jim Thomlinson "Trevor Williams" wrote: Dear All I have a simple table (with a header row) containing 5 columns - 3 of the columns will need to be sorted by the user. Above each of the columns that need sorting there is a shape that is used to run the sorting macro. Here's what I'd like to do. 1 - Rather than creating 3 macros to sort 3 columns I'd like to create one that runs depending on which button, (or shape in this case), is selected. 2 - Depending on the current order of the sorted list, I'd like the macro to do the opposite - i.e. if the list is currently xlAscending, next time the button is pressed for that list, it sorts in xlDescending. Code below - any help appreciated Trevor Williams. -------- Sub SortEachCol() Dim shp As Shape Set shp = ActiveSheet.Shapes(Application.Caller) myCol = shp.TopLeftCell.Column 'this returns a column number, not letter 'this checks to see last sort order and sets myOrder to the opposite order If Range("L13") = "xlAscending" Then myOrder = "xlDescending" Else myOrder = "xlAscending" End If 'this sorts the list - I need to chnge the Range("C:C") to Range(myCol) 'and set Order1:=myOrder Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 'this updates the new sort order Range("L13") = myOrder End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2002 SUM with a twist... | Excel Worksheet Functions | |||
XL2002 - SeriesCollection.Index | Excel Programming | |||
xl2002 problem | Excel Programming | |||
Semi-hang in XL2002 | Excel Discussion (Misc queries) | |||
Using min and max function XL2002 | Excel Worksheet Functions |