Macro hangs during application?

[club rat]

Hello, I am have written a macro to perform certain functions and it hangs during the process.
Being a Novice Excel user, I am not sure if this is normal or if there is a problem causing this effect.
Below is my macro and attached is my workbook.

Thanks in advance to the many people who help in this wonderful forum.

Club Rat

Please Login or Register  to view this content.

[BeachRock]

Hi club rat,

I think the problem you are experiencing with hanging is a direct result of the formulas you are using on the Calculations sheet and how they are referencing entire rows on the DiffCalcs sheet instead of just column A through column W.

Example: On the Calculations sheet, cell J6

Instead of : =AVERAGE(SMALL(INDEX(DiffCalcs!6:6, 1, 4):INDEX(DiffCalcs!6:6, 1, 23),1))

Use: =AVERAGE(SMALL(INDEX(DiffCalcs!$A6:$W6, 1, 4):INDEX(DiffCalcs!$A6:$W6, 1, 23),1)))

This shortens down the number of columns the formula has to look through. I had the same problem with a pool league workbook I made to track stats throughout a season. It was taking upwards of 5 minutes or more for a similar macro I was using to complete.

If you want to get rid of the #NUM! in Calculations!$J$6:$s$179, change the formulas in column A to =IF(Players!$A6="","",Players!$A6) and copy down, then modify your formulas in Calculations!$J$6:$s$179 to be like the following.

=IF($A6="","",AVERAGE(SMALL(INDEX(DiffCalcs!$A6:$W6, 1, 4):INDEX(DiffCalcs!$A6:$W6, 1, 23),1)))

You can do the same for the cells returning #N/A

See the attachment. It takes about 5 to 10 seconds for the macro to run on my PC with the changes described above.