VBA - WorksheetFunction - AverageIfs

[Noz2k]

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

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")
Y = DateAdd("m", 6, X)
lngLastRMD = ws2.Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (Y)
r = 0
Do Until X = Y
Z = DateAdd("m", 1, X)
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
X = DateAdd("m", 1, X)
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?

 

[RoryA]

Try:

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))​

​

 

[Noz2k]

Thank you, that works perfectly.

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

 

[RoryA]

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