# VBA - WorksheetFunction - AverageIfs

#### Noz2k

##### Well-known Member
Hey,

I'm currently trying to run some code to do an averageifs formula, where the datevalues change within a loop, and can't figure out why it isn't working.

Here is my code

Code:
``````Dim X           As Date
Dim Y           As Date
Dim Z           As Date
Dim r           As Long
Dim lngLastRMD  As Long
Dim ws1         As Worksheet
Dim ws2         As Worksheet
Set ws1 = Sheets("page1")
Set ws2 = Sheets("RMD_Data")
X = Sheets("page1").Range("Q5")
lngLastRMD = ws2.Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (Y)
r = 0
Do Until X = Y
ws1.Range("B30").Offset(0, r) = WorksheetFunction.AverageIfs(ws2.Range("C2:C" & lngLastRMD), ws2.Range("B2:B" & lngLastRMD), "=" & ws1.Range("Q3"), ws2.Range("A2:A" & lngLastRMD), ">=" & X, ws2.Range("A2:A" & lngLastRMD), "<" & Z)
r = r + 3
Loop``````

The error is occuring on the averageifs line. If I remove the last argument in the averageifs formula the code runs fine, which suggests to me that Z isn't calculating properly, however if I run through the code or use to Msgbox test to find Z, it is showing as expected 1 month larger than X.

Any ideas?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try:
Rich (BB code):
``ws1.Range("B30").Offset(0, r) = WorksheetFunction.AverageIfs(ws2.Range("C2:C" & lngLastRMD), ws2.Range("B2:B" & lngLastRMD), "=" & ws1.Range("Q3"), ws2.Range("A2:A" & lngLastRMD), ">=" & CDbl(X), ws2.Range("A2:A" & lngLastRMD), "<" & CDbl(Z))​``

Thank you, that works perfectly.

Can I ask what the CDbl does? Have not come across this before.

It converts the date to a number value. As it is you are appending a literal date value into the criteria and the function won't like that (just as it wouldn't in a worksheet).

Replies
8
Views
84
Replies
2
Views
214
Replies
4
Views
200
Replies
2
Views
567
Replies
3
Views
176

1,203,461
Messages
6,055,559
Members
444,798
Latest member
PAO1609

### 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.

### Which adblocker are you using?

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

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