Sum Property of the Worksheet Function Class

[DJester]

Any suggestions to work around the sum error?

Runtime Error '1004'
unable to locate the sum property of the worksheetfunction class

Public Function CalcSheet()
Dim NoSales(0 To 11) As Range
Dim DriveOffs(0 To 11) As Range
Dim Voids(0 To 11) As Range
Dim Shortages(0 To 11) As Range
Dim tNoSales As Integer
Dim tDriveOffs As Currency
Dim tVoids As Currency
Dim tShortages As Currency
Dim X As Integer
Dim NSc As Integer
Dim DOc As Integer
Dim VOc As Integer
Dim SHc As Integer
Dim aNoSales As Integer
Dim aDriveOffs As Currency
Dim aVoids As Currency
Dim aShortages As Currency

    'Initialize Total and Average Variables
    aNoSales = 0
    aDriveOffs = 0
    aVoids = 0
    aShortages = 0
    tNoSales = 0
    tDriveOffs = 0
    tVoids = 0
    tShortages = 0

    'Populate Object Arrays
    Set NoSales(0) = Worksheets(1).Range("B3:B33")
    Set NoSales(1) = Worksheets(1).Range("F3:F33")
    Set NoSales(2) = Worksheets(1).Range("J3:J33")
    Set NoSales(3) = Worksheets(1).Range("N3:N33")
    Set NoSales(4) = Worksheets(1).Range("R3:R33")
    Set NoSales(5) = Worksheets(1).Range("V3: V33")
    Set NoSales(6) = Worksheets(1).Range("Z3:Z33")
    Set NoSales(7) = Worksheets(1).Range("AD3:AD33")
    Set NoSales(8) = Worksheets(1).Range("AH3:AH33")
    Set NoSales(9) = Worksheets(1).Range("AL3:AL33")
    Set NoSales(10) = Worksheets(1).Range("AP3:AP33")
    Set NoSales(11) = Worksheets(1).Range("AT3:AT33")
    
    Set DriveOffs(0) = Worksheets(1).Range("C3:C33")
    Set DriveOffs(1) = Worksheets(1).Range("G3:G33")
    Set DriveOffs(2) = Worksheets(1).Range("K3:K33")
    Set DriveOffs(3) = Worksheets(1).Range("O3:O33")
    Set DriveOffs(4) = Worksheets(1).Range("S3:S33")
    Set DriveOffs(5) = Worksheets(1).Range("W3:W33")
    Set DriveOffs(6) = Worksheets(1).Range("AA3:AA33")
    Set DriveOffs(7) = Worksheets(1).Range("AE3:AE33")
    Set DriveOffs(8) = Worksheets(1).Range("AI3:AI33")
    Set DriveOffs(9) = Worksheets(1).Range("AM3:AM33")
    Set DriveOffs(10) = Worksheets(1).Range("AQ3:AQ33")
    Set DriveOffs(11) = Worksheets(1).Range("AU3:AU33")
    
    Set Voids(0) = Worksheets(1).Range("D3:D33")
    Set Voids(1) = Worksheets(1).Range("H3:H33")
    Set Voids(2) = Worksheets(1).Range("L3:L33")
    Set Voids(3) = Worksheets(1).Range("P3:P33")
    Set Voids(4) = Worksheets(1).Range("T3:T33")
    Set Voids(5) = Worksheets(1).Range("X3:X33")
    Set Voids(6) = Worksheets(1).Range("AB3:AB33")
    Set Voids(7) = Worksheets(1).Range("AF3:AF33")
    Set Voids(8) = Worksheets(1).Range("AJ3:AJ33")
    Set Voids(9) = Worksheets(1).Range("AN3:AN33")
    Set Voids(10) = Worksheets(1).Range("AR3:AR33")
    Set Voids(11) = Worksheets(1).Range("AV3:AV33")
        
    Set Shortages(0) = Worksheets(1).Range("E3:E33")
    Set Shortages(1) = Worksheets(1).Range("I3:I33")
    Set Shortages(2) = Worksheets(1).Range("M3:M33")
    Set Shortages(3) = Worksheets(1).Range("Q3:Q33")
    Set Shortages(4) = Worksheets(1).Range("U3:U33")
    Set Shortages(5) = Worksheets(1).Range("Y3:Y33")
    Set Shortages(6) = Worksheets(1).Range("AC3:AC33")
    Set Shortages(7) = Worksheets(1).Range("AG3:AG33")
    Set Shortages(8) = Worksheets(1).Range("AK3:AK33")
    Set Shortages(9) = Worksheets(1).Range("AO3:AO33")
    Set Shortages(10) = Worksheets(1).Range("AS3:AS33")
    Set Shortages(11) = Worksheets(1).Range("AW3:AW33")
    
    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(NoSales(X)) > 0 Then 'Check for no data
            NSc = NSc + Application.WorksheetFunction.Count(NoSales(X)) 'Count number of data entries
            tNoSales = tNoSales + Application.WorksheetFunction.Sum(NoSales(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1
        
        If Application.WorksheetFunction.Sum(DriveOffs(X)) > 0 Then 'Check for no data
            DOc = DOc + Application.WorksheetFunction.Count(DriveOffs(X)) 'Count number of data entries
            tDriveOffs = tDriveOffs + Application.WorksheetFunction.Sum(DriveOffs(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(Voids(X)) > 0 Then 'Check for no data
            VOc = VOc + Application.WorksheetFunction.Count(Voids(X)) 'Count number of data entries
            tVoids = tVoids + Application.WorksheetFunction.Sum(Voids(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(Shortages(X)) > 0 Then 'Check for no data
            SHc = SHc + Application.WorksheetFunction.Count(Shortages(X)) 'Count number of data entries
            tShortages = tShortages + Application.WorksheetFunction.Sum(Shortages(X)) 'Sum data
        End If
    
    Next X

    'Error Check before Dividing for Average
    If (tNoSales > 0) And (NSc > 0) Then aNoSales = tNoSales / NSc
    If (tDriveOffs > 0) And (DOc > 0) Then aDriveOffs = tDriveOffs / DOc
    If (tVoids > 0) And (VOc > 0) Then aVoids = tVoids / VOc
    If (tShortages > 0) And (SHc > 0) Then aShortages = tShortages / SHc

    'Insert Averages
    Worksheets(1).Range("B37").Value = aNoSales
    Worksheets(1).Range("C37").Value = aDriveOffs
    Worksheets(1).Range("D37").Value = aVoids
    Worksheets(1).Range("E37").Value = aShortages
    
    'Insert Totals
    Worksheets(1).Range("B38").Value = tNoSales
    Worksheets(1).Range("C38").Value = tDriveOffs
    Worksheets(1).Range("D38").Value = tVoids
    Worksheets(1).Range("E38").Value = tShortages
    
    'Release Objects
    Set NoSales(0) = Nothing
    Set NoSales(1) = Nothing
    Set NoSales(2) = Nothing
    Set NoSales(3) = Nothing
    Set NoSales(4) = Nothing
    Set NoSales(5) = Nothing
    Set NoSales(6) = Nothing
    Set NoSales(7) = Nothing
    Set NoSales(8) = Nothing
    Set NoSales(9) = Nothing
    Set NoSales(10) = Nothing
    Set NoSales(11) = Nothing
    
    Set DriveOffs(0) = Nothing
    Set DriveOffs(1) = Nothing
    Set DriveOffs(2) = Nothing
    Set DriveOffs(3) = Nothing
    Set DriveOffs(4) = Nothing
    Set DriveOffs(5) = Nothing
    Set DriveOffs(6) = Nothing
    Set DriveOffs(7) = Nothing
    Set DriveOffs(8) = Nothing
    Set DriveOffs(9) = Nothing
    Set DriveOffs(10) = Nothing
    Set DriveOffs(11) = Nothing
    
    Set Voids(0) = Nothing
    Set Voids(1) = Nothing
    Set Voids(2) = Nothing
    Set Voids(3) = Nothing
    Set Voids(4) = Nothing
    Set Voids(5) = Nothing
    Set Voids(6) = Nothing
    Set Voids(7) = Nothing
    Set Voids(8) = Nothing
    Set Voids(9) = Nothing
    Set Voids(10) = Nothing
    Set Voids(11) = Nothing
        
    Set Shortages(0) = Nothing
    Set Shortages(1) = Nothing
    Set Shortages(2) = Nothing
    Set Shortages(3) = Nothing
    Set Shortages(4) = Nothing
    Set Shortages(5) = Nothing
    Set Shortages(6) = Nothing
    Set Shortages(7) = Nothing
    Set Shortages(8) = Nothing
    Set Shortages(9) = Nothing
    Set Shortages(10) = Nothing
    Set Shortages(11) = Nothing

End Function

 

[DJester]

Sorry, I prefer not to download workbooks.

If recreating the workbook solves the problem, that's probably the best solution!

I completely understand; you're being so nice by trying to help me, I appreciate it.

I'm not sure a msgbox can return a range object?

Yeah I didn't think it would, each array value is range of 30 cells.

 

[RoryA]

The reason for the error is that you have errors in the cells, as was suggested. Try declaring a variant variable called varSum and then use:

    For X = 0 To 11 Step 1
        varSum = Application.Sum(NoSales(X))
        If Not IsError(varSum) Then
            If varSum > 0 Then 'Check for no data
                NSc = NSc + Application.Count(NoSales(X)) 'Count number of data entries
                tNoSales = tNoSales + varSum 'Sum data
            End If
        End If
    Next X

    For X = 0 To 11 Step 1
        varSum = Application.Sum(DriveOffs(X))
        If Not IsError(varSum) Then
            If varSum > 0 Then 'Check for no data
                DOc = DOc + Application.Count(DriveOffs(X)) 'Count number of data entries
                tDriveOffs = tDriveOffs + varSum 'Sum data
            End If
        End If
        
    Next X

    For X = 0 To 11 Step 1
        varSum = Application.Sum(Voids(X))
        If Not IsError(varSum) Then
            If varSum > 0 Then 'Check for no data
                VOc = VOc + Application.Count(Voids(X)) 'Count number of data entries
                tVoids = tVoids + varSum 'Sum data
            End If
        End If
    Next X

    For X = 0 To 11 Step 1
        varSum = Application.Sum(Shortages(X))
        If Not IsError(varSum) Then
    
            If varSum > 0 Then 'Check for no data
                SHc = SHc + Application.Count(Shortages(X)) 'Count number of data entries
                tShortages = tShortages + varSum 'Sum data
            End If
        End If
    Next X

HTH

 

[DJester]

The reason for the error is that you have errors in the cells, as was suggested. HTH

One Gallon of Awesome Sauce to you sir!
The I was referencing too many cells, the last two entries in the arrays shouldn't have been there.

Thanks to everyone!
*Hugs* All around