Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort
Hi all,
I have a worksheet that has 106 columns, some of them have dates, some have text, etc. I want to write a macro to allow users to sort the list in ascending order based on the column of their choice, by asking the user to enter the letter of the alphabet located on top of the column. Im using excel 2003. I dont seem to get the code right yet. I would appreciate it if someone tells me where I went wrong: Sub SORT() code that will select the range to be sorted Ask user which column they want to sort by Dim ColumnToSort as String ColumnToSort = InputBox (Please enter the index of the column you wish to sort by) If ColumnToSort = then Exit Sub End If Here is where Im stuck. I dont know how to use the ColumnToSort as the Key1 I want Key1 to be the cell in Row number 13 and Column ColumnToSort Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort
Try Cells(13,ColumnsToSort)
-- HTH, Barb Reinhardt "Tendresse" wrote: Hi all, I have a worksheet that has 106 columns, some of them have dates, some have text, etc. I want to write a macro to allow users to sort the list in ascending order based on the column of their choice, by asking the user to enter the letter of the alphabet located on top of the column. Im using excel 2003. I dont seem to get the code right yet. I would appreciate it if someone tells me where I went wrong: Sub SORT() code that will select the range to be sorted Ask user which column they want to sort by Dim ColumnToSort as String ColumnToSort = InputBox (Please enter the index of the column you wish to sort by) If ColumnToSort = then Exit Sub End If Here is where Im stuck. I dont know how to use the ColumnToSort as the Key1 I want Key1 to be the cell in Row number 13 and Column ColumnToSort Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort
Dim RngToSort as Range
dim KeyCol as range set rngtosort = nothing on error resume next set rngtosort = application.inputbox(Prompt:="Select the range to sort", _ Type:=8).areas(1) on error goto 0 if rngtosort is nothing then exit sub 'user hit cancel end if set keycol = nothing on error resume next set keycol = application.inputbox(Prompt:="Select a cell in that range!", _ type:=8).cells(1) on error goto 0 if keycol is nothing then exit sub 'user hit cancel end if if keycol.parent.range("a1").address(external:=true) _ < rngtosort.parent.range("a1").address(external:=tru e then msgbox "Key and sort range have to be on the same worksheet!" exit sub end if if intersect(keycol.entirecolumn, rngtosort) is nothing then msgbox "Select a column in the range, please!" exit sub end if rngtosort.sort key1:=keycol, Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Untested, uncompiled. Watch for typos. You don't actually need to specify a row--just a column that's within the range to sort. How do you know that there are no headers in the selected range???????? =========== As an alternative, you may want to look at this from Debra Dalgleish's site: http://contextures.com/xlSort02.html It sorts a predefined set of columns by using invisible rectangles over the header cells. Tendresse wrote: Hi all, I have a worksheet that has 106 columns, some of them have dates, some have text, etc. I want to write a macro to allow users to sort the list in ascending order based on the column of their choice, by asking the user to enter the letter of the alphabet located on top of the column. Im using excel 2003. I dont seem to get the code right yet. I would appreciate it if someone tells me where I went wrong: Sub SORT() code that will select the range to be sorted Ask user which column they want to sort by Dim ColumnToSort as String ColumnToSort = InputBox (Please enter the index of the column you wish to sort by) If ColumnToSort = then Exit Sub End If Here is where Im stuck. I dont know how to use the ColumnToSort as the Key1 I want Key1 to be the cell in Row number 13 and Column ColumnToSort Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort
If you are going to use Range() as your Sort Key then you would need to get
the column letter in the input box Like: ColumnToSort = InputBox (Please enter the index of the column you wish to sort by) ColumnToSort = UCase(ColumnToSort) SortKeyRange = ColumnToSort & 13 Selection.Sort Key1:=Range(SortKeyRange), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Otherwise, use the Cells configuration that Barb suggested. If you don't want rows 1 thru 12 included in the sort, then you will need to define the sort range as well. Otherwise, you can get undesired results. If ColumnToSort = then "Tendresse" wrote: Hi all, I have a worksheet that has 106 columns, some of them have dates, some have text, etc. I want to write a macro to allow users to sort the list in ascending order based on the column of their choice, by asking the user to enter the letter of the alphabet located on top of the column. Im using excel 2003. I dont seem to get the code right yet. I would appreciate it if someone tells me where I went wrong: Sub SORT() code that will select the range to be sorted Ask user which column they want to sort by Dim ColumnToSort as String ColumnToSort = InputBox (Please enter the index of the column you wish to sort by) If ColumnToSort = then Exit Sub End If Here is where Im stuck. I dont know how to use the ColumnToSort as the Key1 I want Key1 to be the cell in Row number 13 and Column ColumnToSort Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort
Dave, thanks for your prompt reply. From what i can see, you are letting
users select the range to be sorted .. well i already know the range and i have only one line in the code that selects it without having to involve the user in this process. It's simply: Range("A13:Corner").Select and i know that row 13 is not the header of the table .. so this is also sorted. All i need from the user is to enter the index of the column they want to sort by. That's all. I see that you are asking the user to select a cell in the column they want to sort by. But i already have the range selected ("A13:Corner"). Selecting another range will deselct the range that needs to be sorted. Rather than asking the user to select a cell within the range, i'm still more inclined to ask them to simply type the letter that refers to the column (the letter at the very top end of each column). and then use this letter somehow as the KEY. I'm heading to work now and i'll try Barb's solution and see if it will work. Please don't think i'm just being stubborn .. i'm only trying to choose the simpler option. Please let me know if from my answer you can see that i'm still missing something ... or if you can see that my method in solving this code would still leave some loose ends. You are the expert and i totally trust your expertise. "Dave Peterson" wrote: Dim RngToSort as Range dim KeyCol as range set rngtosort = nothing on error resume next set rngtosort = application.inputbox(Prompt:="Select the range to sort", _ Type:=8).areas(1) on error goto 0 if rngtosort is nothing then exit sub 'user hit cancel end if set keycol = nothing on error resume next set keycol = application.inputbox(Prompt:="Select a cell in that range!", _ type:=8).cells(1) on error goto 0 if keycol is nothing then exit sub 'user hit cancel end if if keycol.parent.range("a1").address(external:=true) _ < rngtosort.parent.range("a1").address(external:=tru e then msgbox "Key and sort range have to be on the same worksheet!" exit sub end if if intersect(keycol.entirecolumn, rngtosort) is nothing then msgbox "Select a column in the range, please!" exit sub end if rngtosort.sort key1:=keycol, Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Untested, uncompiled. Watch for typos. You don't actually need to specify a row--just a column that's within the range to sort. How do you know that there are no headers in the selected range???????? =========== As an alternative, you may want to look at this from Debra Dalgleish's site: http://contextures.com/xlSort02.html It sorts a predefined set of columns by using invisible rectangles over the header cells. Tendresse wrote: Hi all, I have a worksheet that has 106 columns, some of them have dates, some have text, etc. I want to write a macro to allow users to sort the list in ascending order based on the column of their choice, by asking the user to enter the letter of the alphabet located on top of the column. Iâm using excel 2003. I donât seem to get the code right yet. I would appreciate it if someone tells me where I went wrong: Sub SORT() ☠code that will select the range to be sorted ☠Ask user which column they want to sort by Dim ColumnToSort as String ColumnToSort = InputBox (âœPlease enter the index of the column you wish to sort byâ) If ColumnToSort = âœâ then Exit Sub End If â˜Here is where Iâm stuck. I donât know how to use the ColumnToSort as the Key1 â˜I want Key1 to be the cell in Row number 13 and Column ColumnToSort Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort
Replace this:
set rngtosort = nothing on error resume next set rngtosort = application.inputbox(Prompt:="Select the range to sort", _ Type:=8).areas(1) on error goto 0 with set rngtosort = activesheet.range("a13:x99") I don't know what a13:corner means. Tendresse wrote: Dave, thanks for your prompt reply. From what i can see, you are letting users select the range to be sorted .. well i already know the range and i have only one line in the code that selects it without having to involve the user in this process. It's simply: Range("A13:Corner").Select and i know that row 13 is not the header of the table .. so this is also sorted. All i need from the user is to enter the index of the column they want to sort by. That's all. I see that you are asking the user to select a cell in the column they want to sort by. But i already have the range selected ("A13:Corner"). Selecting another range will deselct the range that needs to be sorted. Rather than asking the user to select a cell within the range, i'm still more inclined to ask them to simply type the letter that refers to the column (the letter at the very top end of each column). and then use this letter somehow as the KEY. I'm heading to work now and i'll try Barb's solution and see if it will work. Please don't think i'm just being stubborn .. i'm only trying to choose the simpler option. Please let me know if from my answer you can see that i'm still missing something ... or if you can see that my method in solving this code would still leave some loose ends. You are the expert and i totally trust your expertise. "Dave Peterson" wrote: Dim RngToSort as Range dim KeyCol as range set rngtosort = nothing on error resume next set rngtosort = application.inputbox(Prompt:="Select the range to sort", _ Type:=8).areas(1) on error goto 0 if rngtosort is nothing then exit sub 'user hit cancel end if set keycol = nothing on error resume next set keycol = application.inputbox(Prompt:="Select a cell in that range!", _ type:=8).cells(1) on error goto 0 if keycol is nothing then exit sub 'user hit cancel end if if keycol.parent.range("a1").address(external:=true) _ < rngtosort.parent.range("a1").address(external:=tru e then msgbox "Key and sort range have to be on the same worksheet!" exit sub end if if intersect(keycol.entirecolumn, rngtosort) is nothing then msgbox "Select a column in the range, please!" exit sub end if rngtosort.sort key1:=keycol, Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Untested, uncompiled. Watch for typos. You don't actually need to specify a row--just a column that's within the range to sort. How do you know that there are no headers in the selected range???????? =========== As an alternative, you may want to look at this from Debra Dalgleish's site: http://contextures.com/xlSort02.html It sorts a predefined set of columns by using invisible rectangles over the header cells. Tendresse wrote: Hi all, I have a worksheet that has 106 columns, some of them have dates, some have text, etc. I want to write a macro to allow users to sort the list in ascending order based on the column of their choice, by asking the user to enter the letter of the alphabet located on top of the column. Iâm using excel 2003. I donât seem to get the code right yet. I would appreciate it if someone tells me where I went wrong: Sub SORT() ☠code that will select the range to be sorted ☠Ask user which column they want to sort by Dim ColumnToSort as String ColumnToSort = InputBox (âœPlease enter the index of the column you wish to sort byâ) If ColumnToSort = âœâ then Exit Sub End If â˜Here is where Iâm stuck. I donât know how to use the ColumnToSort as the Key1 â˜I want Key1 to be the cell in Row number 13 and Column ColumnToSort Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort
Thank you all for your help ..
Barb: your suggestion worked perfectly .. thanks heaps .. Dave: Range("A13:Corner") is the range i want to sort. I just defined a name 'Corner' to cell DB50 as the range will change every time the user adds new rows (or delete existing rows). JLGWhiz: I tried your solution but for some reason the macro doesn't like this "&" symbol! Everytime i run it, this symbol gets highlighted and i get an error message "Type Mismatch" .. any idea why this happens? "JLGWhiz" wrote: If you are going to use Range() as your Sort Key then you would need to get the column letter in the input box Like: ColumnToSort = InputBox (Please enter the index of the column you wish to sort by) ColumnToSort = UCase(ColumnToSort) SortKeyRange = ColumnToSort & 13 Selection.Sort Key1:=Range(SortKeyRange), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Otherwise, use the Cells configuration that Barb suggested. If you don't want rows 1 thru 12 included in the sort, then you will need to define the sort range as well. Otherwise, you can get undesired results. If ColumnToSort = then "Tendresse" wrote: Hi all, I have a worksheet that has 106 columns, some of them have dates, some have text, etc. I want to write a macro to allow users to sort the list in ascending order based on the column of their choice, by asking the user to enter the letter of the alphabet located on top of the column. Im using excel 2003. I dont seem to get the code right yet. I would appreciate it if someone tells me where I went wrong: Sub SORT() code that will select the range to be sorted Ask user which column they want to sort by Dim ColumnToSort as String ColumnToSort = InputBox (Please enter the index of the column you wish to sort by) If ColumnToSort = then Exit Sub End If Here is where Im stuck. I dont know how to use the ColumnToSort as the Key1 I want Key1 to be the cell in Row number 13 and Column ColumnToSort Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Macro | Excel Discussion (Misc queries) | |||
Sort Macro | Excel Discussion (Misc queries) | |||
macro sort | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Using Macro to sort without clicking on macro button | Excel Discussion (Misc queries) |