Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to build sort statement?
I'd like to build a macro that does a number of user-specified things to a
file. One of them is to sort. The user would specify up to 3 columns to sort. Can an Excel macro "build" the sort parameters? The following sure doesn't work: s = "Key1:=Range(""A2""), Order1:=xlAscending, " & _ "Key2:=Range(""B2""), Order2:=xlAscending, " Range("A1:H6563").Sort s Thanks for the help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to build sort statement?
Try this with 3 columns
Sub Macro1() Range("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending, _ Key2:=Range("B2"), Order2:=xlAscending, _ Key3:=Range("C2"), Order3:=xlAscending, _ Header:=xlGuess, Orientation:=xlTopToBottom End Sub If this post helps click Yes --------------- Jacob Skaria "Norm" wrote: I'd like to build a macro that does a number of user-specified things to a file. One of them is to sort. The user would specify up to 3 columns to sort. Can an Excel macro "build" the sort parameters? The following sure doesn't work: s = "Key1:=Range(""A2""), Order1:=xlAscending, " & _ "Key2:=Range(""B2""), Order2:=xlAscending, " Range("A1:H6563").Sort s Thanks for the help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to build sort statement?
If you are looking at a statement no. Sort Method expects the below arguments
to be passed..all of which are optional.. expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try this with 3 columns Sub Macro1() Range("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending, _ Key2:=Range("B2"), Order2:=xlAscending, _ Key3:=Range("C2"), Order3:=xlAscending, _ Header:=xlGuess, Orientation:=xlTopToBottom End Sub If this post helps click Yes --------------- Jacob Skaria "Norm" wrote: I'd like to build a macro that does a number of user-specified things to a file. One of them is to sort. The user would specify up to 3 columns to sort. Can an Excel macro "build" the sort parameters? The following sure doesn't work: s = "Key1:=Range(""A2""), Order1:=xlAscending, " & _ "Key2:=Range(""B2""), Order2:=xlAscending, " Range("A1:H6563").Sort s Thanks for the help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to build sort statement?
Your only option might be have a single sub with some optional parameters
and 3 separate .Sort calls depending on how many Keys (and sort directions) get passed. The SortRange, Key1 and Direction1 would be required, and the other keys and directions optional. Tim "Norm" wrote in message ... I'd like to build a macro that does a number of user-specified things to a file. One of them is to sort. The user would specify up to 3 columns to sort. Can an Excel macro "build" the sort parameters? The following sure doesn't work: s = "Key1:=Range(""A2""), Order1:=xlAscending, " & _ "Key2:=Range(""B2""), Order2:=xlAscending, " Range("A1:H6563").Sort s Thanks for the help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to build sort statement?
its perhaps easier than you'd think....
there must always be one param, plus optional several more...this code allows up to 3 Option Explicit Sub main() 'demo UserSort "A" UserSort "B", "C", "E" End Sub Sub UserSort(param1 As String, ParamArray addl() As Variant) Select Case UBound(addl, 1) Case -1 Range("A1").CurrentRegion.Sort Range(param1 & "1") Case 0 Range("A1").CurrentRegion.Sort Range(param1 & "1"), , _ Key2:=Range(addl(0) & "1") Case 1 Range("A1").CurrentRegion.Sort Range(param1 & "1"), , _ Key2:=Range(addl(0) & "1"), Key3:=Range(addl(1) & "1") End Select End Sub "Norm" wrote: I'd like to build a macro that does a number of user-specified things to a file. One of them is to sort. The user would specify up to 3 columns to sort. Can an Excel macro "build" the sort parameters? The following sure doesn't work: s = "Key1:=Range(""A2""), Order1:=xlAscending, " & _ "Key2:=Range(""B2""), Order2:=xlAscending, " Range("A1:H6563").Sort s Thanks for the help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to build a If statement | Excel Worksheet Functions | |||
Build whre statement | Excel Programming | |||
How to build effective IF Statement for recurring value in cells....sensitivity analysis | Excel Discussion (Misc queries) | |||
If statement with some sort of lookup function | Excel Worksheet Functions | |||
How to build build a macro that automatically imports | Excel Programming |