Re: Can I use a variable as a sort key in VB?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 bellissimo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:DF310706-B955-4677-A82E-9674720F03B7@xxxxxxxxxxxxxxxx
> > 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 Bellissimo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> > > message news:DAD8B851-7374-4548-A1B4-A7E55F0F6408@xxxxxxxxxxxxxxxx
> > > > 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...
> > >
> > >
> > >
>
>
>
.

Relevant Pages