Creating a UDF

Greetings all,
I am trying to create a function that adds up a range numbers based on the value of a different range of cells. The function will be used in a cell in my sheet, and the range that is passed to it is $C4:$O4. Each item in this range contains either a "Yes" or a "No". Based on my research, it seems that the range would best be converted into an array when passed into the Function (but maybe not?), and then the array elements are scanned through to determine the action on it. For each "Yes", I want to accumulate a value contained in row 2 of the same column. The values in Row 2 are decimals that add up to 1.0, each of which is a weight ranking of the categories of all the "Yes" tallies in row 4. "Yes" and "No" is like Pass/Fail. You only get credit of the weighted value if you PASS the item. However, all I get in the cell that houses the Function is "#Value!". I guess this means that I'm not using the Function correctly somehow, because it doesn't seem even be getting into the code of the Function before failing. I had thought this would be simple thing, but I must be doing something incorrectly that is very basic. (NOTE: The column O is a catchall for Auto Failure. If its a YES, then the score is 0% regardless of what the other scores are.)

Any help getting past the error is much appreciated.

' Below should be an image of the sheet. If not, I have attached the entire file. The TEMPLATE sheet is the one I am working on. Column Q row 4 is where the Function is stored.
Capture.JPG


This is my function code (UDF).

Function CalcResult(Rng As Range) As Single
' This Function calculates the result score.
' This adds up thescore weights for each item that is Not a NO.
' If the Autofail column is set to you, the Resutling Score is 0%
'==========================================
Dim Score As Single, K As Integer, CurrentRow As Integer
Dim MyArray() As Variant

MyArray() = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range(Rng)))
'Set MyArray() = Range(Rng)
Score = 0#

If MyArray(UBound(MyArray)) <> "Yes" Then ' Autofail is not true
For K = 1 To UBound(MyArray) ' columns for scores in row 2
If MyArray(K) <> "No" Then ' add the score value
' Accumulate passing score weighted values
Score = Score + Application.Cells(2, K + 2).Value
End If
Next
End If

CalcResult = Score

End Function

Formula:

Please Login or Register  to view this content.

This formula does the trick but be careful because there is a ALT+ENTER character in de column header name

Please Login or Register  to view this content.

It can be made better no doubt but is does what is needed
Check the 100% fail with a If Formula

You guys are amazing. Thank you so much, and I'm so happy I joined this forum.