Help with VB code

[Setsanto]

Basically for my team's scouting database I'm writing a scoring thing. Recently I had to make some changes to the number of spreadsheets, what information is on each one, etc. to fit what my team wanted. I thought I had changed everything correctly, bu it turns out I haven't and its not working. This is a link to the CD-Media page its on: http://www.chiefdelphi.com/media/papers/1914

If anyone can offer any help, it would be much appreciated.

~Nick/1334

[kramarczyk]

So, what do you want it to do?

[Setsanto]

I'm trying to get the macro "CopyGame" to copy the RP and QP on the spreadsheet "Scoresheet" to the RP and QP columns on the spreadsheet "QP&RP"

[kramarczyk]

Here's my shot at it...

http://www.chiefdelphi.com/media/papers/1916

I combined the QR&RP sheet and the Seeding sheet into the Match Results Sheet... They are still in the workbook, just hidden to reduce confusion.

The macros are a bit brute force, but they work... nuff said.

Let me know what you think.

[Setsanto]

Well, it works, which is better than what I did, also, Ranking Points are divided by 3 for their final score, and finally, your IF satements in the totals columns seem kind of pointless, could you please specify what they are for?

~Nick/1334

EDIT: The statement in the last column should actually be
=IF(ISERROR(SUMIF($C$2:$GT$2,"RP",C3:GT3)/(COUNT(C3:GT3)/2)),0,SUMIF($C$2:$GT$2,"RP",C3:GT3)/(COUNT(C3:GT3)/4))/3

[kramarczyk]

The IF statements add up the totals of QP and RP's... since I kept with the original format of QP RP QP RP QP RP... I needed a way to seperate them out which is what the countif's do. The QP's just get added up so nothing else is required, but the RP's need to get averaged per section 9.3.8, not divided by 3.

The total number of ranking points earned by a team throughout their qualification matches, divided by the number of matches played (excluding any SURROGATE matches), then truncated to two decimal places, will be their ranking score.

Since there is no automated 'average if' function I needed to build it... I used the sumif to add up the RP points and the count to see how many entries there are. However, since the QP and RP are listed in the same row I end up with a count of 6 for 3 matches, hence I divided the count by 2. sumif / count / 2. The ISERROR is just capturing any divide by zero errors that may occur because there are no entries for a team yet. The errors don't cause any harm, but they are ugly.

I'm not sure what the '/4 ' & '/3' tagged on the end of the suggested if statement do. Also, the formula in the iserror segment and the formula in the false section of the if should be the same for the error capturing to work as designed.

I acknowledge that the VB code could be cleaned up and made more elegent, but I was unable to follow the original code due to the lack of comments.

Mark

[Setsanto]

I wasn't criticizing you at all, I really appreciate all the help that you have given me over the past few days. My comments were merely there because of my own misinterpretation of the rules, and my lack of knowledge in excel/VB. Thanks for theclarification and all your help.

~Nick/1334

[kramarczyk]

No offense taken, I was just trying to provide a thorough answer.

Hope it works out well for you.

Mark

[Setsanto]

Thnaks, good luck to your team too.