Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Help with input box to select a sort up to 28
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Help with input box to select a sort up to 28
I am trying to use an input box so when I press the box opens and ask for the
line number via a list of the type of sort I want to do, but I am finding
that the sort does not work correctly.
Can somebody help me with my code.
Thank you
Ken
Sub All_Sorts1()
'
Dim MySort As Long
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlDescending,
Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7,247
Default Help with input box to select a sort up to 28
It appears that all you are really doing is changing the sort keys
based on the user's input. Try some code like the following. Change
the SortKey ranges for each value according to what you want to do.

Sub AAA()
Dim RangeToSort As Range
Dim SortKey1 As Range
Dim SortKey2 As Range
Dim SortKey3 As Range

Dim L As Long
Set RangeToSort = Range(Selection(1, 1), _
Selection.SpecialCells(xlLastCell))
L = CLng(InputBox( _
"Enter a number between 1 and 28.", Type:=1))
Select Case L
Case 1
Set SortKey1 = Range("C4")
Set SortKey2 = Range("D4")
Set SortKey3 = Range("E4")
Case 2
Set SortKey1 = Range("K4")
Set SortKey2 = Range("L4")
Set SortKey3 = Range("M4")
'.... through Case 28

Case Else
MsgBox "Invalid input."
Exit Sub
End Select

RangeToSort.Sort key1:=SortKey1, order1:=xlAscendin, _
key2:=SortKey2, order2:=xlAscending, _
key3:=SortKey3, order3:=xlAscending, _
Header:=xlGuess
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Fri, 5 Mar 2010 10:23:01 -0800, Ken
wrote:

I am trying to use an input box so when I press the box opens and ask for the
line number via a list of the type of sort I want to do, but I am finding
that the sort does not work correctly.
Can somebody help me with my code.
Thank you
Ken
Sub All_Sorts1()
'
Dim MySort As Long
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("D4"), Order1:=xlDescending,
Key2:=Range("G4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select
MySort = CLng(Application.InputBox(Prompt:="1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, or
28", Type:=1))
If MySort < 1 _
Or MySort 28 Then
Exit Sub
End If
Range("C4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("J4") _
, Order2:=xlAscending, Key3:=Range("I4"), Order3:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B1:B3").Select

End Sub
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select and input data from a list highway Excel Discussion (Misc queries) 1 April 3rd 09 03:42 PM
Select the first number/numbers in a cell input into another colum Sue Excel Discussion (Misc queries) 5 April 14th 08 08:20 PM
sheet protection - only selected range to be able to select/input data Corey Excel Worksheet Functions 7 February 13th 07 05:41 PM
How do I input numbers used to select cells in several columns BC2233 Excel Discussion (Misc queries) 3 February 16th 06 05:35 PM
Select cell from range based on input in excel xp dingy101 Excel Discussion (Misc queries) 3 November 20th 05 12:05 AM


All times are GMT +1. The time now is 08:43 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement