VBA AverageIfs () Error

[Gulume]

I am having some issues to calculate an average number using WorksheetFunction.AverageIfs() with 5 different conditions (each one in a different range).

When I run the code a Run time Error occurs (Unable to get the AverageIfs properties of the WorkSheet function).

There are two workbooks that I use, one with the database (data.xlsx) and another that I consolidate the data.
What I am trying to do is to calculate the average number of days that i have to perform each set of contracts that start on the same day for each starting date (some contracts start on the same date but have different deadlines).
Data regarding contract duration is available in the database.

What I observed is that the error occurs after the first date iteration probably because the second outcome is #DIV/0! (I tried using the excel formula and that's what happened - maybe the system doesn't deal very well with such error).

What should I do to avoid such problem? How can I circumvent this issue?

Thanks

Sub AvgDeliveryDay()

Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim AvgRng As Range 'the range I want to calculate an average
Dim CritRng As Range '1st criteria range - Product range
Dim CritRng2 As Range '2nd criteria range - Branch range
Dim CritRng3 As Range '3rd criteria range - Port Name range
Dim CritRng4 As Range '4th criteria range - Transshipment range
Dim CritRng5 As Range '5th criteria range - Start Delivery date range
Dim Product As String 'the 1st criteria - Product name
Dim Branch16 As String 'the 2nd criteria - Branch number
Dim Port As String 'the 3rd criteria - Port
Dim Trans1 As String 'the 4th criteria - Transshipment location
Dim DelivDT As Date 'the 5th criteria - Start Delivery Date
Dim lr As Long
Dim i As Long

Set wkb = Excel.Workbooks("data.xlsx")
Set wks = wkb.Worksheets("Sheet1")

'Count Lines
lr = wks.Cells(Rows.Count, "A").End(xlUp).Row

'Delivery time
Set AvgRng = wks.Range("AB4:AB" & lr)
'Product Name
Set CritRng = wks.Range("M4:M" & lr)
'Branch
Set CritRng2 = wks.Range("C4:C" & lr)
'Port
Set CritRng3 = wks.Range("AL4:AL" & lr)
'Transhipment location
Set CritRng4 = wks.Range("AI4:AI" & lr)
'Start Date Delivery
Set CritRng5 = wks.Range("X4:X" & lr)

Product = "CORN"
Branch16 = "501"
Port = "RIO GRANDE"
Trans1 = "CRUZ ALTA"
i = 4

'Calculate the average number of days a contract can be performed for a specific delivery start date in column B
Do Until i = 371

DelivDT = Sheets("RG").Cells(i, 2)
Sheets("RG").Cells(i, 6).Value = Application.WorksheetFunction.AverageIfs(AvgRng, CritRng, Product, CritRng2, Branch16, CritRng3, Port, CritRng4, Trans1, CritRng5, DelivDT)

i = i + 1
Loop

End Sub

 

[Kjtakke]

At a glance you could try using some error handling using VBA on error (on error resume next) .... or wrap in an IfError(.... function.
I am more than happy to have a look for you; however, I would need to have an example copy to test any solutions.

 

[Gulume]

But in what part I would have to insert the code to handle the error? Before the Do Until or one line before the WorksheetFunction.AverageIfs ?

 

[Kjtakke]

But in what part I would have to insert the code to handle the error? Before the Do Until or one line before the WorksheetFunction.AverageIfs ?

Once the code “on error resume next” has been stated, all subsequent code with errors will be skipEd.

To return to normal errors insert “on error GoTo 0”

This means you can place the “on error resume next” code anywhere after the start of the Sub and before the loop.

It is important to note that this will not necessarily solve your problem, just allows the rest of your code to execute.

More effective and complex error handling using If statements to determine if your data is going to cause an error and if so insert code to fix or skip the issue.

If you wanted to go one step further again you can use the on error statement to identify what type of error it is and respond accordingly.

Hope this helps.

 

[Fluff]

How about

Dim x As Variant
Do Until i = 371

DelivDT = Sheets("RG").Cells(i, 2)
x = Application.AverageIfs(AvgRng, CritRng, Product, CritRng2, Branch16, CritRng3, Port, CritRng4, Trans1, CritRng5, DelivDT)
If Not IsError(x) Then Sheets("RG").Cells(i, 6).Value = x

i = i + 1
Loop

 

[Gulume]

Thank you for the help Kjtakke and Fluff!
Both ideas were very enlightening.

As both of you suggested I used some error handling function to my code but first the on error statement to identify what kind of error I was facing.

The code ended a bit like Fluff code

Do Until i = 371

DelivDT = Sheets("RG").Cells(i, 2)
Sheets("RG").Cells(i, 6).Value = Application.AverageIfs(AvgRng, CritRng, Product, CritRng2, Branch16, CritRng3, Port, CritRng4, Trans1, CritRng5, DelivDT)
If IsError(Sheets("RG").Cells(i, 6).Value) Then
Sheets("RG").Cells(i, 6).Value = 0
End If

i = i + 1
Loop

Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RoryA]

For the future, please note the forum rules on cross-posting and follow them. Thanks.