Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to revise this Sorting code a bit.
Hi all. This code works, but I know there has to be a better way to do it.
Right now it seems "crude" to me. 2 things that bug me are 1) not being able to use set userrange solely by itself to set the range (If I do it pulls the value from the cell not the range). Second, is I thought there was a way to select a cell in a sheet in one line of code like Sheets("Sheet1").Range("A1").select. I am using excel 2000 so maybe this matters. Here is my code: defaultrange = Selection.Address Set UserRange = Application.InputBox(Prompt:="Select Column to Sort by as format X:X ", Title:="Sort", Default:=defaultrange, Type:=8) Sheets("Sort").Select Range("A1000") = UserRange.Address Set SortRange = Range("A1000") Sheets("Main Scorecard (All FSEs)").Select Range("4:28").Copy Sheets("Sort").Select Range("A1").Select Selection.PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False Selection.Sort Key1:=Range(SortRange), Order1:=xlDescending Selection.Copy Sheets("Main Scorecard (All FSEs)").Select Range("A4").Select Selection.PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False It's this part that bugs me Range("A1000") = UserRange.Address Set SortRange = Range("A1000") & Selection.Sort Key1:=Range(SortRange), Order1:=xlDescending I 'should' just use the input box, and use the range selected in that inputbox in the sorting as UserRange. Also, is there a way, someone could just type like the letter D in the inputbox and have the sorting sort by that range? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to revise this Sorting code a bit.
Got Part of it now, I found a much more effective way and more userfriendly
way of obtaining the column the user wants Dim testrange As String Dim SortRangeX As String testrange = InputBox(prompt:="Enter Column Letter", Title:="Sort") SortRangeX = testrange & ":" & testrange so now all you have to do is selection.sort key1:=Range(SortRangeX), Order1:=xlDescending "havocdragon" wrote: Hi all. This code works, but I know there has to be a better way to do it. Right now it seems "crude" to me. 2 things that bug me are 1) not being able to use set userrange solely by itself to set the range (If I do it pulls the value from the cell not the range). Second, is I thought there was a way to select a cell in a sheet in one line of code like Sheets("Sheet1").Range("A1").select. I am using excel 2000 so maybe this matters. Here is my code: defaultrange = Selection.Address Set UserRange = Application.InputBox(Prompt:="Select Column to Sort by as format X:X ", Title:="Sort", Default:=defaultrange, Type:=8) Sheets("Sort").Select Range("A1000") = UserRange.Address Set SortRange = Range("A1000") Sheets("Main Scorecard (All FSEs)").Select Range("4:28").Copy Sheets("Sort").Select Range("A1").Select Selection.PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False Selection.Sort Key1:=Range(SortRange), Order1:=xlDescending Selection.Copy Sheets("Main Scorecard (All FSEs)").Select Range("A4").Select Selection.PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False It's this part that bugs me Range("A1000") = UserRange.Address Set SortRange = Range("A1000") & Selection.Sort Key1:=Range(SortRange), Order1:=xlDescending I 'should' just use the input box, and use the range selected in that inputbox in the sorting as UserRange. Also, is there a way, someone could just type like the letter D in the inputbox and have the sorting sort by that range? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add/Revise data in separate spreadsheets all at once | Excel Worksheet Functions | |||
Revise Macro from Worksheet | Excel Programming | |||
Need to revise formula | Excel Worksheet Functions | |||
Need to revise formula | Excel Worksheet Functions | |||
Need to revise formula | Excel Worksheet Functions |