ExcelBanter - 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...