Unable to get the average property of the worksheetfunction class

Hi,

I have Userform to display some averages by year, and use It on multiple sheets. I get this error on sheets that are currently blank (future years).

How can get rid of this and simply display 0 values If range is empty ?

Sample is attached, open userform on second or third sheet to see error !

Any help appreciated !

Please Login or Register  to view this content.

Hi rcm,

this formula works on worksheet indeed. But I have Userfom to display this result, and code for this is:

Please Login or Register  to view this content.

I believe lines that are red need to be changed, in VBA.

You could add at the beginning of the code

Please Login or Register  to view this content.

hi Trebor76,

I tried your code but could not get it working... Can you post example form my file ?

rcm, on resume next those two errors are not displayed on Userfom, so It's no use to me. I need all data displayed, just zero values for those with error. I guess I need some error handler ?

I figure It out on my own, just forming an error handler does the trick

Please Login or Register  to view this content.

thanks for help both of you !!

How about htis one. You start with empty combobox, when selecting year you go to that page and calculations are made instantly.

Hi Bakerman, thanks for post, your solution is also fine, but I'll stick to my own.

Thanks for help !