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