Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
Hi,
I am trying to write a piece of code which will present users with an input box (asking for a number) which will then select the current range and sort it using the variable defined by the input box. How would I use the numeric variable as the key to sorting my data. Example: mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The problem must be around my storing a number as the variable but the sort command would appear to want a string??? Please help... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
Selection.Sort Key1:= Selection.Cells(1,MyNumber) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Pablo Bellissimo wrote : mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
Thanks you two. Great help. I'm fairly new to this VB stuff so I am
interested in why there are differences in the way you both handled that problem. Bob - What does the CLng bit do that wasn't in the other response. For the record, I used the version without this in as it looked simpler :-) "Bob Phillips" wrote: Dim mynumber mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=Selection.Cells(1, CLng(mynumber)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom -- HTH RP (remove nothere from the email address if mailing direct) "Pablo Bellissimo" <Pablo wrote in message ... Hi, I am trying to write a piece of code which will present users with an input box (asking for a number) which will then select the current range and sort it using the variable defined by the input box. How would I use the numeric variable as the key to sorting my data. Example: mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The problem must be around my storing a number as the variable but the sort command would appear to want a string??? Please help... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
It was because in my test the inputbox returned a string, so I forced it to
a number for the Cells property, otherwise it errored. -- HTH RP (remove nothere from the email address if mailing direct) "pablo bellissimo" <pablo wrote in message ... Thanks you two. Great help. I'm fairly new to this VB stuff so I am interested in why there are differences in the way you both handled that problem. Bob - What does the CLng bit do that wasn't in the other response. For the record, I used the version without this in as it looked simpler :-) "Bob Phillips" wrote: Dim mynumber mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=Selection.Cells(1, CLng(mynumber)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom -- HTH RP (remove nothere from the email address if mailing direct) "Pablo Bellissimo" <Pablo wrote in message ... Hi, I am trying to write a piece of code which will present users with an input box (asking for a number) which will then select the current range and sort it using the variable defined by the input box. How would I use the numeric variable as the key to sorting my data. Example: mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The problem must be around my storing a number as the variable but the sort command would appear to want a string??? Please help... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
Forgot to add that if you define MyNumber as a Long in the first instance,
that casting is not necessary, but too often posters here don't even declare their variables, so they then assume variant type, so I was just being cautious. In reality, my solution and KeepItCool's are identical, his just assumes a long variable, mine assumes it might not be :-) -- HTH RP (remove nothere from the email address if mailing direct) "pablo bellissimo" <pablo wrote in message ... Thanks you two. Great help. I'm fairly new to this VB stuff so I am interested in why there are differences in the way you both handled that problem. Bob - What does the CLng bit do that wasn't in the other response. For the record, I used the version without this in as it looked simpler :-) "Bob Phillips" wrote: Dim mynumber mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=Selection.Cells(1, CLng(mynumber)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom -- HTH RP (remove nothere from the email address if mailing direct) "Pablo Bellissimo" <Pablo wrote in message ... Hi, I am trying to write a piece of code which will present users with an input box (asking for a number) which will then select the current range and sort it using the variable defined by the input box. How would I use the numeric variable as the key to sorting my data. Example: mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The problem must be around my storing a number as the variable but the sort command would appear to want a string??? Please help... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
I understand now. thanks.
Thinking of moving it to the next stage - I would like to change this so that rather than asking the user which column they want to sort by how difficult would it be to display a dropdown list box showing the headers of the available columns. How would I store the selection from the list as the variable to use later. I am not just sorting the data by this variable but i use the same variable leter in the module to split the raange and perform some calculations. Maybe I'm getting out of my depth... "Bob Phillips" wrote: Forgot to add that if you define MyNumber as a Long in the first instance, that casting is not necessary, but too often posters here don't even declare their variables, so they then assume variant type, so I was just being cautious. In reality, my solution and KeepItCool's are identical, his just assumes a long variable, mine assumes it might not be :-) -- HTH RP (remove nothere from the email address if mailing direct) "pablo bellissimo" <pablo wrote in message ... Thanks you two. Great help. I'm fairly new to this VB stuff so I am interested in why there are differences in the way you both handled that problem. Bob - What does the CLng bit do that wasn't in the other response. For the record, I used the version without this in as it looked simpler :-) "Bob Phillips" wrote: Dim mynumber mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=Selection.Cells(1, CLng(mynumber)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom -- HTH RP (remove nothere from the email address if mailing direct) "Pablo Bellissimo" <Pablo wrote in message ... Hi, I am trying to write a piece of code which will present users with an input box (asking for a number) which will then select the current range and sort it using the variable defined by the input box. How would I use the numeric variable as the key to sorting my data. Example: mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The problem must be around my storing a number as the variable but the sort command would appear to want a string??? Please help... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
You could create a combobox from the control toolbox an d load it in a
separate macro like so Sub LoadCombo() Dim col As Range With ActiveSheet .ComboBox1.Clear For Each col In .UsedRange.Columns .ComboBox1.AddItem "Column " & ColumnLetter(col.Column) Next col .ComboBox1.ListIndex = 0 End With End Sub Function ColumnLetter(ColNumber As Integer) As String ColumnLetter = Left(Cells(1, ColNumber).Address(False, False), _ 1 - (ColNumber 26)) End Function and then ujse the c ombo click event to fire the sort Private Sub ComboBox1_Change() Dim rng As Range Range("A1").Select Set rng = ActiveCell.CurrentRegion rng.Sort Key1:=rng.Cells(1, Replace(ComboBox1.Value, "Column ", "")), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End Sub -- HTH Bob Phillips "pablo bellissimo" <pablo wrote in message ... I understand now. thanks. Thinking of moving it to the next stage - I would like to change this so that rather than asking the user which column they want to sort by how difficult would it be to display a dropdown list box showing the headers of the available columns. How would I store the selection from the list as the variable to use later. I am not just sorting the data by this variable but i use the same variable leter in the module to split the raange and perform some calculations. Maybe I'm getting out of my depth... "Bob Phillips" wrote: Forgot to add that if you define MyNumber as a Long in the first instance, that casting is not necessary, but too often posters here don't even declare their variables, so they then assume variant type, so I was just being cautious. In reality, my solution and KeepItCool's are identical, his just assumes a long variable, mine assumes it might not be :-) -- HTH RP (remove nothere from the email address if mailing direct) "pablo bellissimo" <pablo wrote in message ... Thanks you two. Great help. I'm fairly new to this VB stuff so I am interested in why there are differences in the way you both handled that problem. Bob - What does the CLng bit do that wasn't in the other response. For the record, I used the version without this in as it looked simpler :-) "Bob Phillips" wrote: Dim mynumber mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=Selection.Cells(1, CLng(mynumber)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom -- HTH RP (remove nothere from the email address if mailing direct) "Pablo Bellissimo" <Pablo wrote in message ... Hi, I am trying to write a piece of code which will present users with an input box (asking for a number) which will then select the current range and sort it using the variable defined by the input box. How would I use the numeric variable as the key to sorting my data. Example: mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The problem must be around my storing a number as the variable but the sort command would appear to want a string??? Please help... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a variable as a sort key in VB?
Hi Bob (and anyone else that can assist)
Similar to this problem, I have a dropdown containing the column headers in a worksheet called "Main" (Cell D38), and I want to sort the data in "Report" by the choice the user makes in Main!D38. Can you assist with a bit of magic on the code (assuming we can recycle what is shown below)? Thanks "Bob Phillips" wrote: You could create a combobox from the control toolbox an d load it in a separate macro like so Sub LoadCombo() Dim col As Range With ActiveSheet .ComboBox1.Clear For Each col In .UsedRange.Columns .ComboBox1.AddItem "Column " & ColumnLetter(col.Column) Next col .ComboBox1.ListIndex = 0 End With End Sub Function ColumnLetter(ColNumber As Integer) As String ColumnLetter = Left(Cells(1, ColNumber).Address(False, False), _ 1 - (ColNumber 26)) End Function and then ujse the c ombo click event to fire the sort Private Sub ComboBox1_Change() Dim rng As Range Range("A1").Select Set rng = ActiveCell.CurrentRegion rng.Sort Key1:=rng.Cells(1, Replace(ComboBox1.Value, "Column ", "")), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End Sub -- HTH Bob Phillips "pablo bellissimo" <pablo wrote in message ... I understand now. thanks. Thinking of moving it to the next stage - I would like to change this so that rather than asking the user which column they want to sort by how difficult would it be to display a dropdown list box showing the headers of the available columns. How would I store the selection from the list as the variable to use later. I am not just sorting the data by this variable but i use the same variable leter in the module to split the raange and perform some calculations. Maybe I'm getting out of my depth... "Bob Phillips" wrote: Forgot to add that if you define MyNumber as a Long in the first instance, that casting is not necessary, but too often posters here don't even declare their variables, so they then assume variant type, so I was just being cautious. In reality, my solution and KeepItCool's are identical, his just assumes a long variable, mine assumes it might not be :-) -- HTH RP (remove nothere from the email address if mailing direct) "pablo bellissimo" <pablo wrote in message ... Thanks you two. Great help. I'm fairly new to this VB stuff so I am interested in why there are differences in the way you both handled that problem. Bob - What does the CLng bit do that wasn't in the other response. For the record, I used the version without this in as it looked simpler :-) "Bob Phillips" wrote: Dim mynumber mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=Selection.Cells(1, CLng(mynumber)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom -- HTH RP (remove nothere from the email address if mailing direct) "Pablo Bellissimo" <Pablo wrote in message ... Hi, I am trying to write a piece of code which will present users with an input box (asking for a number) which will then select the current range and sort it using the variable defined by the input box. How would I use the numeric variable as the key to sorting my data. Example: mynumber = InputBox("Please enter the column number that you want the data splitting by") Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Sort Key1:=mynumber, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The problem must be around my storing a number as the variable but the sort command would appear to want a string??? Please help... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for variable sort order | Excel Discussion (Misc queries) | |||
VBA Variable Range Sort | Excel Discussion (Misc queries) | |||
Variable Column Sort | New Users to Excel | |||
How do I use a variable in a sort macro | Excel Worksheet Functions | |||
Sort : how can I use a variable in a VB sort function? | Excel Worksheet Functions |