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