Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need help auto-sorting a data table

  Asked By: Bakir    Date: Nov 22    Category: MS Office    Views: 899
  

Here's what I'm trying to do:

I want a data table to automatically sort when I change a value in a
particular column. It's a football worksheet, and a player changing
teams will affect the totals, which is what I want to sort by (in
descending order).

Column A = Player name
Column B = Team
Column C - S = Weeks 1 through 17
Column T = Sum of columns C through S (the column which I want the
data sorted by)

Data table covers cells A1:T84.

If more info is needed, let me know. I am totally ignorant to VBA
in Excel, so this will either have to be cut-n-paste or VERY
simplified.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Patty Freeman     Answered On: Nov 22

Which column?

The following code does it for a change  in column  B of the table  as
signified by the second line's two appearances of "$B$2:$B$84". Right
click the tab of the sheet with the table in and choose 'View Code'
and paste the following in the topmost pane (the one with two dropdown
lists at the top):

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, Range("$B$2:$B$84")).Address = "$B$2:$B$84" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("A1:T84").Select
Selection.Sort Key1:=Range("T2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Application.EnableEvents = True
Target.Select
Application.ScreenUpdating = True
End If
End Sub

Go back to the sheet and test it (I've assumed the table has headers).

 
Answer #2    Answered By: Johnathan Nelson     Answered On: Nov 22

This works wonerfully...I don't really understand the reason it
works, but I surely appreciate your help. You even selected the
right column  in which changes would be made! Can you recommend a
good tutorial in getting started with VBA for Excel? There's a lot
of things I'm sure I do more difficult than need be.

 
Answer #3    Answered By: Horia Ahmed     Answered On: Nov 22

My suggestion is - don't.

Two reasons:

(1) This is pretty advanced stuff. As you say, you don't know any VBA. This is
not a good place to start. Get into VBA in a more gentle fashion.

(2) Sorting based on a change  to a cell is error-prone. If the user actually
wants to change to cells, side by side, or two cells  one under the other, then
the sort  will happen after the first cell is changed, and the user will then
probably go on to change the wrong cell next. (I'm speaking from experience - I
implemented just such a sorting method, but withdrew it hastily when people
starting making mistakes or getting confused.)

What I tend to do is to put a button at the top of the sheet to re-sort on
command. This leaves it under user control, but makes it much easier than going
through the menus.

Just create a control-toolbox button, then double-click it while in design mode.
This will open up the VB editor, and create a "click" subroutine, and you can
type in a sort command. If you type:

Call Range("A1").Sort(

then the pop-up assistance will guide you through the parameters needed  to do
your sort. For instance, one from one of my sheets:

Call Range("A1").Sort(Key1:=Range("E1"), Key2:=Range("F1"), Key3:=Range("A1"),
Header:=xlYes)

does a sort on three columns  (E, F and A). They are all defaulted to ascending.
There is a header row.

As you can see, this is the same as you would find if you pulled up the sort
command from the Data menu.

And that's the only line you need inside your click subroutine.

 
Didn't find what you were looking for? Find more on Need help auto-sorting a data table Or get search suggestion and latest updates.




Tagged: