SiGill

I am putting together a league table in Excel for my local Pool League. I've added a formula so that when I enter each person's points Excel automatically tells me what position that person is in. How can I now put in a formula that automatically sorts the rows so that the the person in position 1 is automatically at the top and the person in position 10 is automatically at the bottom, etc

ChefDude

A2:B6 is the grid area to sort
B6 is the column to sort on

stick it in the sheet code
Private Sub Worksheet_Change(ByVal Target As Range)
Range("A2:B6").Select
Range("B6").Activate
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2").Select
End Sub

ChefDude

prolly should make the last line Target.Select

so the cursor stays on the cell you updated

SiGill

Thanks for the Reply Chef Dude. But could you put it in more of a dummy format. I'm totally lost.

Thanks

Simon

ChefDude

ok, you need the top left cell (A2 for instance) to the bottom right cell (say, B6) of your rows you want to sort

if you press [ALT][F11] you get behind the scenes in Excel. Double click on (Sheet1) and paste in this code.

where it says A2, that should be the cell at the top left
B6 should be replaced with the bottom right cell

B2 should be the top cell in the column to be sorted on.

SiGill

Makes a bit more sense, I have done all that and pasted in everything, gone back to my spreadsheet but I get the following message when I put information into those cells. "Run-time error '1004': Aplication-defined or object-defined error" It then gives me the following options.
End Debug or Help

SiGill

It then highlights the below 3 lines

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Your help is really appreciated.

Simon