Worksheet Function.Sum issues-CodePudding

This could be a simple one but i cant figure it out. Maybe i've spent too long behind the screen today. I'm wanting to run the below code to add up the values in several cells within a worksheet. They are grouped in lines of 6 with 6 ranges in each (hasn't come up that way in code below) for easier editing down the line but when i try to run it i get 'compile error: wrong number of arguments or invalid property assignments' someone please tell me where I'm going wrong here. cheers

'weekend
't-8

 SMRY.Range("AE8") = Application.WorksheetFunction.Sum(Sheet1.Range("W23"), Sheet1.Range("W77"), Sheet1.Range("W131"), Sheet1.Range("W185"), Sheet1.Range("W239"), Sheet1.Range("W239"), _
                                                       Sheet1.Range("W349"), Sheet1.Range("W403"), Sheet1.Range("W457"), Sheet1.Range("W511"), Sheet1.Range("W565"), Sheet1.Range("W619"), _
                                                       Sheet1.Range("W675"), Sheet1.Range("W729"), Sheet1.Range("W783"), Sheet1.Range("W837"), Sheet1.Range("W891"), Sheet1.Range("W945"), _
                                                       Sheet1.Range("W1001"), Sheet1.Range("W1055"), Sheet1.Range("W1109"), Sheet1.Range("W1163"), Sheet1.Range("W1217"), Sheet1.Range("W1271"), _
                                                       Sheet1.Range("W1327"), Sheet1.Range("W1381"), Sheet1.Range("W1435"), Sheet1.Range("W1489"), Sheet1.Range("W1543"), Sheet1.Range("W1597"), _
                                                       Sheet1.Range("W1653"), Sheet1.Range("W1707"), Sheet1.Range("W1761"), Sheet1.Range("W1815"), Sheet1.Range("W1869"), Sheet1.Range("W1923"), _
                                                       Sheet1.Range("W1979"), Sheet1.Range("W2033"), Sheet1.Range("W2087"), Sheet1.Range("W2141"), Sheet1.Range("W2195"), Sheet1.Range("W2249"), _
                                                       Sheet1.Range("W2305"), Sheet1.Range("W2359"), Sheet1.Range("W2413"), Sheet1.Range("W2467"), Sheet1.Range("W2521"), Sheet1.Range("W2575"))

CodePudding user response：

WorksheetFunction.Sum only accepts up to 30 arguments.

The easy fix is to split the calculation into two steps:

Dim tempSum As Double
tempSum = WorksheetFunction.Sum(...) 'first 30 arguments here
tempSum = tempSum   WorksheetFunction.Sum(...) ' remaining arguments here
SMRY.Range("AE8").Value = tempSum

Another option is to create a single range:

SMRY.Range("AE8").Value = WorksheetFunction.Sum(Sheet1.Range("W23,W77,W131,W185,W239")) ' and so on

noting that there's a 255-character limit for the reference passed to Range.

CodePudding user response：

Sum With Row Offset

It looks like there should be a pattern i.e. every 54th cell. When writing so many addresses there may be mistakes e.g. W239 is occurring twice and some offsets are 52 and 56.
You could use Union in a loop to combine all the cells into a range and then sum up the range in one go.

Sub SumWithOffset()
    
    Const srOffset As Long = 54
    Const sCellsCount As Long = 48
    
    Dim sCell As Range: Set sCell = Sheet1.Range("W23")
    Dim srg As Range: Set srg = sCell
    
    Dim sr As Long
    For sr = 2 To sCellsCount
        Set sCell = sCell.Offset(srOffset)
        'Debug.Print sCell.Address
        Set srg = Union(srg, sCell)
    Next sr
    
    SMRY.Range("AE8").Value = Application.Sum(srg)
    
End Sub