Macro for autosorting a competition ladder

[Apey]

Hi all, first post. I have little experience in Excel and recently I've started working on a few fun projects to improve my skills.

I have created a competition table for a sporting a competition. The table has various column headers but of most importance is F/A & Points. The data to be sorted is in K6:T21 (Column S contains F/A, T contains Points) and the column headers are in row 5 (K5, L5,...,T5). It is to be sorted firstly by Points descending then F/A descending, like competition tables commonly are. I want it to autosort if any of the values in F/A or Points change.

I also have two other competition tables that are Home and Away competition ladders that I would like to be autosorted in the same way. Would it be easier to create a new macro for each or possible to include in the one macro?

Finally, if I also have a macro for manually sorting the tables by other columns (such as Wins, Losses, For, Against, etc), will this cause issues with the autosort macro?

I have searched for solutions elsewhere but seeing as I have no experience in VBA I couldn't really follow the other solutions to similar problems and alter them to suit my specific problem.

If necessary, I can upload a copy of the sheet.

Any help would be greatly appreciated.

Edit: Worksheet added

NRLLadderPredictor.xlsm

[jmac1947]

Hello Apey,

Welcome to the forums

It is always a huge bonus (that is it generally means someone will quickly look at your problem) if you always include a sample work book with enough data to be meaningful in terms of your request and if appropriate your expected results (in this the text is pretty clear on results expected.

So, post a sample and the forum members will have a crack at assisting you

[Apey]

Hello Apey,

Welcome to the forums

It is always a huge bonus (that is it generally means someone will quickly look at your problem) if you always include a sample work book with enough data to be meaningful in terms of your request and if appropriate your expected results (in this the text is pretty clear on results expected.

So, post a sample and the forum members will have a crack at assisting you

Yeah fair enough, I was being a bit lazy not uploading it haha.

I'll edit it into the first post to make it easier to read the problem in its entirety

Just to add, the ladder is generated as the scores are entered. Nothing in the table is altered directly.

[jmac1947]

Hi Apey,

Given that the ladder is not much use until the round has been completed it may be easier to simply put a "sort button" on the page and use a "normal macro" rather than try to trap a change behind the actual page automatically.

The auto change can be done but beware it will trigger (generally speaking) if any cell changes, not just the ones you are interested in.

Just a random thought

Jmac

[Apey]

Hi Apey,

Given that the ladder is not much use until the round has been completed it may be easier to simply put a "sort button" on the page and use a "normal macro" rather than try to trap a change behind the actual page automatically.

The auto change can be done but beware it will trigger (generally speaking) if any cell changes, not just the ones you are interested in.

Just a random thought

Jmac

Thanks for the reply.

There are two reasons I want it to sort after any cell changes.

1) The official ladder for this competition is updated after every single game, so I would prefer it do the same.

2) This project is also going to double as a 'ladder predictor' whereby people can add games and see the table change as games are entered.

However, if what you're saying is, having this inbuilt 'autosort' macro is going to play havoc with having a manual sort for individual columns such as For, Against and F/A, then I understand where you're coming from. You said that the auto change will trigger if any cell changes - does it also trigger from a regular sort action?

[jmac1947]

to be honest I don't know the answer to your question, I am reasonably new to the amazing world of "worksheet_events" processing.

My gut guess is that it will usually do that so you would need to be specific about which cells changing should be processed and which ones are to effectively be ignored.

I will try to get some time tomorrow to have a play

Jmac