Excel help

spudato7

Hi,
Im having trouble with excel im trying to make a world cup league table and so far its going well but i have no way of it auto sorting so that the team with the most points goes to the top also i have a function in that if there is a draw it changes the team stats to have one draw one game played and 1 point because they equal eachother before you type anything in is there anyway to delay this? thank you

srsly78

http://msdn.microsoft.com/en-us/library/aa213656%28office.11%29.aspx

spudato7

thanks for the reply but im not looking for a timer its more that I want it to update when I type it in

srsly78

http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change

Worksheet_Change event will fire when a cell has been changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("H5")) Is Nothing Then DoSomething()
End Sub

spudato7

thanks for th reply do you know how to set the table to automatically put the team with the most points to the top?

srsly78

You know you can just type the question into google right?

http://www.mrexcel.com/forum/excel-questions/604428-how-sort-data-columns-visual-basic-applications-instead-excels-sort-function.html

spudato7

i know but i was looking for someone to put it simpy for me because im not too advanced

sopretty

Just select the entire table by highlighting it and click on the Data Sorting tool on the menu on top. Select the column you wish to sort by (i.e. the scores) and sort it either ascending (ranging from lowest value to highest value, or Descending, if you wish the highest value to appear at the top).

srsly78

I assume the op wants to do it programmatically through vba, not by clicking on column.

spudato7

ive been doing what sopretty has said but i want it to happen when i change the value so i dont have to resort it everytime

sopretty

srsly78 wrote: »

I assume the op wants to do it programmatically through vba, not by clicking on column.

Why did ya have to complicate things lol. Didn't realise what forum this was in. Was just browsing thread titles. :P

Chazz Michael Michaels

spudato7 wrote: »

ive been doing what sopretty has said but i want it to happen when i change the value so i dont have to resort it everytime

Why not?

sopretty

spudato7 wrote: »

ive been doing what sopretty has said but i want it to happen when i change the value so i dont have to resort it everytime

If you change a figure in a field which is involved in a formula, the formula should update the field in which the formula is in.

Do it simply

Column A : Enter digit 2
Column B: Enter digit 4
Column C: Enter formula = A+B (depending what cells you're in).

Then change a figure in either columns A or B and see whether C updates?

sopretty

Just to mention, you'll have to click out of a cell for it to update.

MagicMarker

sopretty wrote: »

If you change a figure in a field which is involved in a formula, the formula should update the field in which the formula is in.

Do it simply

Column A : Enter digit 2
Column B: Enter digit 4
Column C: Enter formula = A+B (depending what cells you're in).

Then change a figure in either columns A or B and see whether C updates?

The OP says he wants the table to sort automatically when values are changed. What are you replying to?

sopretty

MagicMarker wrote: »

The OP says he wants the table to sort automatically when values are changed. What are you replying to?

Oh. I thought he wanted the field to update. My mistake.

namenotavailablE

The demo file available at this link sounds like it does the general idea of what you want to do:

https://app.box.com/s/j0diridxw857xv9sib7h

Avantech

Hi Spudato,

The most straight forward way to do it is to record a macro that sorts the list as you want. Then go to the VB editor and have a look in the Module(should be Module1). Here you will find the code for the recorded macro. Copy this code out into the Microsoft Excel Objects section (for the sheet you are changing).

Paste the code in here but surround it with (Don't copy the initial opening Sub command - example below or closing Sub Command)

Sub Macro1()
'
' Macro1 Macro
'

'
.
.
.
.
.
.
.
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

So you will have something like

Private Sub Worksheet_Change(ByVal Target As Range)
Range("B1:C6").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("B1:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Now when you change any value on the sheet this macro will be fired and the table will update as you want.

Regards,
Avantech