worksheetfunction.averageifs not working...

[seekyourway]

Hi there,

I have the following code...

When I click on the button, i get a run-time error of 1004: "unable to get the averageifs property of worksheetfunction class......

Can I even use the averageifs in VBA?

Has anybody ran into this problem before?

Private Sub CommandButton1_Click()
'calculating Average in Days for VGH clinic, CCM by period
Dim b5 As Double
Dim AClinicElapsedDay As Range
Dim AClinicPeriod As Range
Dim mPeriod As Range
Dim AClinicAnalyzable As Range
Dim lrow As Integer

Set mPeriod = Worksheets("Test").Range("B2")
lrow = Worksheets("A").Range("A2").End(xlUp).Row
Set AClinicElapsedDay = Worksheets("A").Range("B2:B" & lrow)

Set AClinicPeriod = Worksheets("A").Range("A2:A" & lrow)
Set AClinicAnalyzable = Worksheets("A").Range("C2:C" & lrow)

b5 = Application.WorksheetFunction.AverageIfs(AClinicElapsedDay, AClinicPeriod, mPeriod, AClinicAnalyzable, "Yes")

Worksheets("Test").Range("B5") = b5

End Sub

 

[Jerry Sullivan]

Hi seekyourway,

The averageifs error is a result of a typo in this line...

lrow = Worksheets("A").Range("A2").End(xlUp).Row

This will return lrow=1 and averageifs errors because your ranges are the headers instead of the data.

You probably meant to do this...

lrow = Worksheets("A").Cells(Rows.Count, "A").End(xlUp).Row