Combine IFERROR with WorksheetFunction.CountIfs

[reberryjr]

I have around 75 lines of code similar to the ones below. Each line can result in an error for multiple reasons (basically there's nothing to count or total). I cannot seem to find anything on the web that will show me how to add IFERROR in each line. Essentially, when there's an error b/c there's nothing to count or total, I just want the textbox value to be 0.

Me.txt_PAppInSLA = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), "Approved", mP.Range("AF:AF"), "In SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))
Me.txt_PConInSLA = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), "Conditional Approved", mP.Range("AF:AF"), "In SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))
Me.txt_PDenInSLA = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), "Denied", mP.Range("AF:AF"), "In SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))
Me.txt_PInSLAGT = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("AF:AF"), "In SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))

'Calcualtes the count of PRF decisions made outside of SLA.
Me.txt_PAppOutSLA = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), "Approved", mP.Range("AF:AF"), "Out of SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))
Me.txt_PConOutSLA = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), "Conditional Approved", mP.Range("AF:AF"), "Out of SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))
Me.txt_PDenOutSLA = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), "Denied", mP.Range("AF:AF"), "Out of SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))
Me.txt_POutSLAGT = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("AF:AF"), "Out of SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))

'Calculates the total decisions made.
txt_PAppTotal = Val(Me.txt_PAppInSLA.value) + Val(Me.txt_PAppOutSLA.value)
txt_PConTotal = Val(Me.txt_PConInSLA.value) + Val(Me.txt_PConOutSLA.value)
txt_PDenTotal = Val(Me.txt_PDenInSLA.value) + Val(Me.txt_PDenOutSLA.value)
txt_PGT = Val(Me.txt_PInSLAGT.value) + Val(Me.txt_POutSLAGT.value)

 

[Herakles]

CountIfs returns zero if no matching rows are found.

What error do your lines produce?

 

[reberryjr]

CountIfs returns zero if no matching rows are found.

What error do your lines produce?

If an Associate that is picked from a combo box isn't found, then I get an "unable to perform CountIf error"

 

[Herakles]

What is happening between making the selection from the combo box and running the posted code?

 

[reberryjr]

What is happening between making the selection from the combo box and running the posted code?

Just some sorting. The issue arises if the Associate isn't found, or the decision wasn't found (i.e., they approved some stuff but didn't deny stuff). It'll throw the error at this line:

Me.txt_PDenInSLA = WorksheetFunction.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), "Denied", mP.Range("AF:AF"), "In SLA", mP.Range("S:S"), ">=" & CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End))

 

[Herakles]

I have very little to go on.

Can you populate the cobo_Assoc combo box with unique values from the mP.Range("P:P") range?

 

[reberryjr]

I have very little to go on.

Can you populate the cobo_Assoc combo box with unique values from the mP.Range("P:P") range?

Not an option. Looks like if I throw an On Error Resume Next, it'll give me the 0 I'm looking for in most fields. I'll just do If Then statements for the rest.

 

[Alex Blakenburg]

I couldn't test it but try this:

With Application
    Me.txt_PDenInSLA = .IfError(.CountIfs(mP.Range("P:P"), Me.cobo_Assoc, mP.Range("T:T"), _
                        "Denied", mP.Range("AF:AF"), "In SLA", mP.Range("S:S"), ">=" & _
                        CDate(Me.txt_Start), mP.Range("S:S"), "<=" & CDate(Me.txt_End)), _
                        0)
End With