Rank top 4 scores and assign points (Or Equals)

[ctild]

I not used to using VB very much so I'm looking for help with a points awarding code for a scoring system. I've looked for similar codes that I might be able to amend, but haven’t managed to find what I need.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a list of scores in column B starting at row 5 ("B5"). <o></o>
The list may contain blanks for a non return, but will also be variable depending on the number of attendees, so it will need to find the bottom row. <o></o>
<o></o>
Application.Goto Cells(Rows.Count, "B").End(xlUp)<o></o>
Range(Selection, Range("B5")).Select<o></o>
<o></o>
What I need is a way to assign points to the top 4 scores "or equal" and paste the points awarded into column H starting at row 5 ("H5").<o></o>
<o></o>
e.g. <o></o>
The top score gets 5 points. <o></o>
If two scores are equal 2nd they both get 4 points.<o></o>
The 3rd highest score gets 3 points.<o></o>
If two scores are equal 4th they both get 2 points. <o></o>
<o></o>
Basically don't just allocate the points to the top 5 scores only.<o></o>
<o></o>
If possible, I would also like to allocate a default point of "1", for all other entries found in Column B, that wasn't awarded a point as above. (i.e. You get 1pt for turning up)<o></o>
<o></o><o></o>
I realise this might be a little complicated but any help to a Newbie appreciated.<o></o>
<o></o>

 

[ctild]

This is really annoying; I’ve spent a couple of days on this now.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I had the code working fine, but then I needed to re-design the worksheet and introduce my Add or Delete a player code. Unfortunately I might not be the only person using this file so I’m trying to automate this as much as possible for those will less PC skills.<o></o>
<o> </o>
At present if I want to add a player, I’m finding all cells from ("B10") down to the last cell with a value in Column AK, - then moving them down 1 Row. I then copy some formatted ‘donor cells’ and insert them back in at the top of the range, ("B10:AK10") - enter the new players name in ("B10”) & ("AL10”) using a Userform. Then I use an Ascending "Sort", based on the Players first Name in column B within the Range ("B10:AK60") - putting them back in alphabetical order.<o></o>
<o> </o>
The problem is, after the inserting the new Player name and doing a “Sort”, the associated Formulas loose their original references and don't work as before. <o></o>
<o> </o>
So what I think I need now is a way of re-inserting the correct formulas using VBA to replace all the Formulas after either inserting a new row, or deleting at row when a player leaves. <o></o>
<o> </o>
<o> </o>
(I'm assuming that 60 will be the Max number of members required)<o></o>
<o> </o>
How I’ve set it up:-<o></o>
I use column “AJ” for all the player’s names, starting at (“AJ10:AJ60”) downward. The player’s scores are then inputted into the cell adjacent to their name (“AK10:AK60”) using another Userform. The formula for awarding points to the top 4 scores also starts adjacent, at (“AL10:AK60”). Finally the Top 4 Scores also starts adjacent at (“AM10:AM13”).<o></o>
<o> </o>
<o> </o>
Any further help appreciated.<o></o>
<o> </o>

 

[T. Valko]

This is really annoying; I’ve spent a couple of days on this now.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I had the code working fine, but then I needed to re-design the worksheet and introduce my Add or Delete a player code. Unfortunately I might not be the only person using this file so I’m trying to automate this as much as possible for those will less PC skills.<o></o>
<o></o>
At present if I want to add a player, I’m finding all cells from ("B10") down to the last cell with a value in Column AK, - then moving them down 1 Row. I then copy some formatted ‘donor cells’ and insert them back in at the top of the range, ("B10:AK10") - enter the new players name in ("B10”) & ("AL10”) using a Userform. Then I use an Ascending "Sort", based on the Players first Name in column B within the Range ("B10:AK60") - putting them back in alphabetical order.<o></o>
<o></o>
The problem is, after the inserting the new Player name and doing a “Sort”, the associated Formulas loose their original references and don't work as before. <o></o>
<o></o>
So what I think I need now is a way of re-inserting the correct formulas using VBA to replace all the Formulas after either inserting a new row, or deleting at row when a player leaves. <o></o>
<o></o>
<o></o>
(I'm assuming that 60 will be the Max number of members required)<o></o>
<o></o>
How I’ve set it up:-<o></o>
I use column “AJ” for all the player’s names, starting at (“AJ10:AJ60”) downward. The player’s scores are then inputted into the cell adjacent to their name (“AK10:AK60”) using another Userform. The formula for awarding points to the top 4 scores also starts adjacent, at (“AL10:AK60”). Finally the Top 4 Scores also starts adjacent at (“AM10:AM13”).<o></o>
<o></o>
<o></o>
Any further help appreciated.<o></o>
<o></o>

I'm not much of a programmer so I won't be of much help with this.

 

[ctild]

Finally sorted it.
I wasn't asking for the Vb codes to insert the new player, just how to insert the modified Formulas in the correct positions.

I just kept coming up with errors, which may have been my problem not understanding how the formula worked.

But finally managed to Record the correctly referenced Array formulas correctly.

Selection.FormulaArray = _
"Modified code here"


Thanks again for all your time & assistance.

 

[T. Valko]

Finally sorted it.
I wasn't asking for the Vb codes to insert the new player, just how to insert the modified Formulas in the correct positions.

I just kept coming up with errors, which may have been my problem not understanding how the formula worked.

But finally managed to Record the correctly referenced Array formulas correctly.

Selection.FormulaArray = _
"Modified code here"


Thanks again for all your time & assistance.

Good deal. Thanks for feeding back!