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

 

[cornflakegirl]

Welcome to the board.

Your syntax looks okay to me. Which line are you getting the error on?

 

[VoG]

Are there error values (like #N/A) in the range that you are trying to sum?

 

[DJester]

Welcome to the board.

Your syntax looks okay to me. Which line are you getting the error on?

Debugger breaks on the first use of Application.Worksheetfunctions.Sum()
And on any use of it in these FOR statements

    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

Are there error values (like #N/A) in the range that you are trying to sum?

I use NA() elsewhere in the sheet, but not in the ranges in question.

Microsoft KB is of no help, and only suggests using a SUB rather than a function; which I tried, but it produced the same results.

I may have to break down and write my own SUMCELLS(RANGE) function rather than rewrite all the modules in my workbook. Everything was working fine until I switched from using Application.Worksheetfunctions.sumif to Application.Worksheetfunctions.sum

 

[cornflakegirl]

Have you tested the worksheet ranges, using =SUM() in the actual worksheet? Because, as VoG said, it's probably the data in your sheet that's the problem rather than the code.

 

[Kaps_mr2]

you could put a msgbox in each of the FOR NEXT LOOPS so determine what values are there :-


For X = 0 To 11 Step 1
Msgbox DriveOffs(X)
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

Thanks

Kaps

</pre>

 

[DJester]

Have you tested the worksheet ranges, using =SUM() in the actual worksheet? Because, as VoG said, it's probably the data in your sheet that's the problem rather than the code.

Yes they are summed in other columns

I copied just this module and the Worksheet it works with to a new workbook if you would like to take a look you might understand what I am working with.

http://www.d-jester.com/temp/Workbook.xls

I just hope its not an error indigenous to my version of excel. (xl2000 9.0.6926 SP-3)

 

[DJester]

you could put a msgbox in each of the FOR NEXT LOOPS so determine what values are there :-


For X = 0 To 11 Step 1
Msgbox DriveOffs(X)
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

Results in Debugger throwing a Type Mismatch flag

 

[cornflakegirl]

Sorry, I prefer not to download workbooks.

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

 

[cornflakegirl]

Results in Debugger throwing a Type Mismatch flag

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