Errors with User Defined Function VBA

Hi everyone,
I have two UDFs designed to calculate averages based of the values offset (0, -1) and (0,-2) to any cells in my given range where the date year = year specified in the function. This is to work out yearly averages. These UDFs have been named AnnAv and AnnAv2 respectively.
I have attached a sample workbook to demonstrate where errors crop up:

Sheet 1:
All calculations work. Test1 has scores and dates recorded and uses AnnAv to find the yearly averages; Test2 has scores ratings and dates recorded, and uses AnnAv2 to find the yearly averages.

Sheet 2:
The introduction of blank spaces for future input creates a #VALUE! error in the outcome of AnnAv2 (bottom table) but works fine for AnnAv (top table).

Sheet 3:
In both cases, averages are being calculated for ratings which are all offset (0, -1) from the date columns, so only AnnAv is used. In the bottom table, the inclusion of words in the range seems to be the cause of the #VALUE! error.

Can figure out why these errors are occurring?

The first thing I did was choose one of the functions and add a stop statement to the top. Then it would enter debug mode (for each copy of the function. I ended up deleting most references to the functions so that I didn't need to wade through all of the instances of the function.) While in debug mode, I could step through the function and found that it errored (without warning or message) on:

Please Login or Register  to view this content.

What this statement is doing is checking if Ccell contains a date and if the year of that date is the same as yr. When Ccell.value is a number or a string that can be interpreted as a date (or empty where the value is interpreted to be the number 0), these tests work just fine. When Ccell.value is a text string that cannot be interpreted as a date, the Year() function errors.

What I think you will need to do is separate these into separate, nested If..Then statements.

Please Login or Register  to view this content.

This way, when Ccell is not a date formatted cell, then it can skip over the Year() function and avoid the runtime error.