Application.WorksheetFunction.SumIfs is no longer working

Question

I have some code that ran the last time I was in a workbook, but no longer runs.  The error message I get is:

"Unable to get the SumIfs property of the Worksheet Function Class"

If I change the function to some other function like SumIf, or cosh, the application.workbookfunction works okay.  This ran without issue the last time I ran it.  The specific code line is:

                    rsrcValue.Cells(lngX, intCol).Value = ThisWorkbook.Application.WorksheetFunction.SumIfs( _

                    arg1:=rHL, arg2:=rC, arg3:=strC, arg4:=rMAOP, arg5:=strMAOP1) + _

                    ThisWorkbook.Application.WorksheetFunction.SumIfs(arg1:=rHL, arg2:=rC, arg3:=strC, _

                    arg4:=rMAOP, arg5:=strMAOP2)

I have also tried this (with srcWB as a workbook objecr being passed to the subroutine

                    rsrcValue.Cells(lngX, intCol).Value = srcWB.Application.WorksheetFunction.SumIfs( _

                    arg1:=rHL, arg2:=rC, arg3:=strC, arg4:=rMAOP, arg5:=strMAOP1) + _

                    srcWB.Application.WorksheetFunction.SumIfs(arg1:=rHL, arg2:=rC, arg3:=strC, _

                    arg4:=rMAOP, arg5:=strMAOP2)

I am running in Excel 2007.  I can't think of any changes I made since last running the code that would have changed the way this runs.

Answer 1

Take a look at the way you've defined these ranges:

Set rC = destWS.Range("O" & lngC, "O" & lngD)  ' Segmentation sheet Class Column

Set rHL = destWS.Range("EK:EK")   ' Segmentation sheet HCA Length

This is one of your sumif's formulas

Application.SumIfs( _

                    arg1:=rHL, arg2:=rC, arg3:=strC, arg4:=rMAOP, arg5:=strMAOP1) + _

                    Application.SumIfs(arg1:=rHL, arg2:=rC, arg3:=strC, _

                    arg4:=rMAOP, arg5:=strMAOP2)

If you look at your range definitions, they need to be the same length to work in the SUMIFS.  Some are entire columns and some are partial columns.   Fix that and let us know if it helps.

Answer 2

I set all of the ranges to the same length and that got rid of the "Type mismatch" error.  Everything runs as it should. 

Thanks,

Answer 3

I would first check what an equivalent function call returns to a worksheet:

ActiveCell.Formula = _

"=SumIfs(" & _

rHL.Address & "," & rC.Address & "," & strC & "," & rMAOP.Address & "," & strMAOP1 & ") + " & _

"SumIfs(" & _

rHL.Address & "," & rC.Address & "," & strC & "," & rMAOP.Address & "," & strMAOP2 & ")"

It could be that there is an unhandled error value in one of the ranges....

Answer 4

Here is the code for the subroutine.  I modified the code to just use Application.SumIfs  I now get a "Type mismatch" error :

' Sub-routine is used to sum data from the segementation sheets and update the sorce sheet with the

' Summary values.  It is called from the'subMAOP_Modify_Files_To_Add_HCA_Columns' subroutine

Sub subMAOP_Sum_Data(destWS As Worksheet, srcWB As Workbook, lngX As Long, srcWS As Worksheet)

On Error GoTo Err_ErrorCode

'  Update stationing in the segmentation sheet

Dim varBegin As Variant, varLen As Variant, varEnd As Variant, varStart As Variant

Dim booCheck As Boolean, booFirst As Boolean, booHCA As Boolean, booFirstRow As Boolean

Dim rHL As Range, rNL As Range, rsrcValue As Range, rMAOP As Range, rC As Range ' Define new HCA column ranges

Dim destLastR As Range

Dim lngC As Long, lngY As Long, lngD As Long

Dim intI As Integer, intJ As Integer, intK As Integer

Dim varHLTotal As Variant, varNLTotal As Variant

Dim dblSum As Double

Dim strC As String, strHCA As String, strMAOP1 As String, strMAOP2 As String

Dim intCol As Integer

' Find Starting Row

booCheck = False

lngC = 1

Do Until booCheck

    varStart = destWS.Range("C" & lngC).Value

    If ThisWorkbook.Application.IsNumber(varStart) Then

        booCheck = True

    Else:

        lngC = lngC + 1

    End If

Loop

Set destLastR = destWS.Cells(Rows.Count, "C").End(xlUp)

lngD = destLastR.Row

'Get and update the rirst Row of Stationing

Set rC = destWS.Range("O" & lngC, "O" & lngD)  ' Segmentation sheet Class Column

Set rHL = destWS.Range("EK:EK")   ' Segmentation sheet HCA Length

Set rNL = destWS.Range("EL:EL")  ' Segmentation sheet non-HCA Length

Set rMAOP = destWS.Range("AO" & lngC, "AO" & lngD)  ' Segmentation sheet non-HCA Length

Set rsrcValue = srcWS.Range("A1")

rsrcValue.Cells(lngX, 14).Value = srcWS.Application.Sum(destWS.Range("C:C"))

intCol = 16

For intI = 1 To 4 ' For Classes 1 to 4

    Select Case intI

        Case Is = 1

            strC = "Class I"

        Case Is = 2

            strC = "Class II"

        Case Is = 3

            strC = "Class III"

        Case Is = 4

            strC = "Class IV"

    End Select

    For intJ = 1 To 2   ' For HCA and Non-HCA

        Select Case intJ

            Case Is = 1

                strHCA = "EK"   ' Count HCA Length

            Case Is = 1

                strHCA = "EJ"   ' Count non-HCA Length

        End Select

        For intK = 1 To 14  ' For each MAOP determining category

            Select Case intK

                Case Is = 1 ' Design Calculation

                    strMAOP1 = "Design Calculation - Original Class"

                    strMAOP2 = "Design Calculation - Current Class"

                Case Is = 2 ' Design Calculation without records

                    strMAOP1 = "Unknown (Verified)"

                    strMAOP2 = ""

                Case Is = 3 ' Pressure Test

                    strMAOP1 = "Pressure Test Pressure"

                    strMAOP2 = "Validation Pressure Test"

                Case Is = 4 ' Pressure Test without records

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Is = 5 ' Granfathered Pressure

                    strMAOP1 = "Grandfather Pressure"

                    strMAOP2 = "Certificated Pressure"

                Case Is = 6 ' Grandfathered pressure without records

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Is = 7 ' Operator Determined Pressure

                    strMAOP1 = "Operator Determined Pressure"

                    strMAOP2 = "XXX"

                Case Is = 8 ' Operator Determined Pressure without Records

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Is = 9 ' Grandfather High Pressure

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Is = 10    ' Grandfather High Pressure without records

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Is = 11    ' Alternative or special permit

                    strMAOP1 = "Alternative Pressure"

                    strMAOP2 = "Special Permit Pressure"

                Case Is = 12    ' Alternative or special permit without records

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Is = 13    ' Other method

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Is = 14    ' Other method witout records

                    strMAOP1 = "XXX"

                    strMAOP2 = "YYY"

                Case Else:

                    strMAOP1 = "<>XXX"

                    strMAOP2 = "<>YYY"

            End Select

            Select Case intJ

                Case Is = 1

                    rsrcValue.Cells(lngX, intCol).Value = Application.SumIfs( _

                    arg1:=rHL, arg2:=rC, arg3:=strC, arg4:=rMAOP, arg5:=strMAOP1) + _

                    Application.SumIfs(arg1:=rHL, arg2:=rC, arg3:=strC, _

                    arg4:=rMAOP, arg5:=strMAOP2)

                Case Is = 2

                    rsrcValue.Cells(lngX, intCol).Value = Application.SumIfs( _

                    arg1:=rNL, arg2:=rC, arg3:=strC, arg4:=rMAOP, arg5:=strMAOP1) + _

                    Application.SumIfs(arg1:=rNL, arg2:=rC, arg3:=strC, _

                    arg4:=rMAOP, arg5:=strMAOP2)

            End Select

        intCol = intCol + 1

        Next intK

    intCol = intCol + 1

    Next intJ

intCol = intCol + 1

Next intI

Exit Sub

Exit_ErrorCode:

    Exit Sub

Err_ErrorCode:

    MsgBox Err.Description

    Resume Exit_ErrorCode

End Sub

Answer 5

First, you don't need to have ThisWorkbook.Application or srcWB.Application, just Application (assuming you are running this within Excel and are not interacting with other Applications (Word, PPT, etc).  

Hopefully rsrcValue is a Worksheet, otherwise, you'll have issues as well.  

It would help to know the value of each argument and if the argument is a range, constant, etc. 

HTH,

Barb Reinhardt