how to use averageifs in VBA

[Rookieeee]

Hello guys,

Currently I have a set of commands in VBA like:

for n = 1 To 274
port_return.Columns(3).Cells(n).Value = WorksheetFunction.AverageIfs(monthlydata.Columns(2), monthlydata.Columns(3), "=W", monthlydata.Columns(5), "<=0.3")

port_return.Columns(4).Cells(n).Value = WorksheetFunction.AverageIfs(monthlydata.Columns(2), monthlydata.Columns(3), "=W", monthlydata.Columns(5), ">0.7")


Next n

However, in this averages function, sometimes there is no cell meeting the criteria in averages(), so this 'For next' loop stops when there is no cell meet those criteria.

What I need to do is that this function returns '0' or blank when no cell meets criteria.

I tried this command

port_return.Columns(4).Cells(n).Value = WorksheetFunction.IfError(WorksheetFunction.AverageIfs(monthlydata.Columns(2), monthlydata.Columns(3), "=W", monthlydata.Columns(5), ">0.7"), "")

but it does not work.

Can you guys please help me out?

 

[Fluff]

Try it like

x = Application.AverageIfs(monthlydata.Columns(2), monthlydata.Columns(3), "=W", monthlydata.Columns(5), "<=0.3")
If Not IsError(x) Then port_return.Columns(3).Cells(n).Value = x

 

[Rookieeee]

Try it like

x = Application.AverageIfs(monthlydata.Columns(2), monthlydata.Columns(3), "=W", monthlydata.Columns(5), "<=0.3")
If Not IsError(x) Then port_return.Columns(3).Cells(n).Value = x

Thank you so much. It works. Additionally, I also tried 'on error resume next', it's also a good choice.

Anyway, thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.
It's also best to avoid 'on error resume next' unless absolutely necessary as it can mask errors & cause other problems.