Thread
:
Can I use a variable as a sort key in VB?
View Single Post
#
9
Posted to microsoft.public.excel.programming
D Zandveld
external usenet poster
Posts: 33
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 With Quote
D Zandveld
View Public Profile
Find all posts by D Zandveld