How to tell the AVERAGE function to avoid non numerical values-CodePudding

I've got a worksheet that looks something like this

and what I want to do is average the values on each column using vba.

If I use the =AVERAGE() formula, that works fine and it ignores the N/A in the columns. But I need to do this with vba.

If I try to do something like

Sub test()

Worksheets("Sheet1").Range("C17").Value = WorksheetFunction.Average("C2:C14")

End Sub

Then I get a message saying "Unable to get the Average property of the WorksheetFunction class"

Is there any way I can tell my code to avoid the "N/A" when it calculates the averages so I don't get this issue?

CodePudding user response：

Since Average doesn't take a string input, use

WorksheetFunction.Average(Worksheets("Sheet1").Range("C2:C14"))

rather than just WorksheetFunction.Average("C2:C14")