Excel Help

nigel3a

Hi,
I'm struggling with an excel spreadsheet. I need to run a macro automatically, when the figures change in a cell. I can write the macro but can't get it to run without physically selecting it first. Any help or vba code would be muchly appreciated

It's for this year's world cup, to update tables, points, play-offs who plays who etc.
I just enter the scores and it does the rest, all except sort the groups into point order, which I have to do manually.
If anyone wants a copy, pm me

johnmc

Try this;
http://support.microsoft.com/kb/213612

andydiysaver

hi
I do developing in my spare time to keep the cash high -




within the SHEET CODE you need this


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'don't loop it, do one operation for one change in events!


If Target.Range = Sheet1.Range ("your cell ref")
'changing cell


'YOUR MACRO HERE BASED ON TARGET CHANGING




Application.EnableEvents = True
'turn it back on

End Sub




sheet2 if it's sheet 2 etc.

nigel3a

Hi,
Thanks for your replies.

I can assign a macro to a button, but I'm not sure if setting out the code as below is what you mean. If you PM me your email, I'll send it you, might be easier to understand.
Nigel

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'don't loop it, do one operation for one change in events!


If Target.Range = Groups.Range("P4:P7") Then
'changing cell


Range("H3:P7").Select
ActiveWorkbook.Worksheets("Groups").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Groups").Sort.SortFields.Add Key:=Range("P4:P7"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Groups").Sort.SortFields.Add Key:=Range("O4:O7"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Groups").Sort
.SetRange Range("H3:P7")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.EnableEvents = True


If Target.Range = Groups.Range("P12:P15") Then
Range("H11:P15").Select
ActiveWorkbook.Worksheets("Groups").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Groups").Sort.SortFields.Add Key:=Range("P12:P15") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Groups").Sort.SortFields.Add Key:=Range("O12:O15") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Groups").Sort
.SetRange Range("H11:P15")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

nigel3a

JohnMC

I want it to sort the order, not necessarily show a message, which I think is what the link is telling me

Bigphil1474

nigel, I've done a similar thing.

Basically, I have all the fixtures with cells for the scores. When I enter the scores it automatically updates the tables with the teams goals for/against (based on cell content), and win/lose/draw based on the 'IF' function i.e. IF d2>d3'w' etc. Multiply the number of W's by 3, add number of D's to get total points. To rank the teams I used the following function [=RANK.EQ(S3,S3:S6,0)] which ranks them based on total points (points in S3 to S6). Only fall down is that teams with the same points have the same rank. Couldn't be bothered sorting that out, but it does the trick for me, to manually transfer teams to second round. It doesn't re-order the teams within the table either, but not a big issue for me.
Currently being used to work out predictions for the final, and so far I've predicted the full set of fixtures 10 different times, and got Argentina v. Brazil to play each other in the final each time, even when throwing in the odd highly unlikely scenario such as England making it to the semis.