worksheetfunction.averageifs not working...

seekyourway

New Member
Joined
Nov 1, 2011
Messages
21
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?

Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi seekyourway,

The averageifs error is a result of a typo in this line...
Code:
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...
Code:
lrow = Worksheets("A").Cells(Rows.Count, "A").End(xlUp).Row
 
Upvote 0

Forum statistics

Threads
1,215,869
Messages
6,127,415
Members
449,382
Latest member
DonnaRisso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top