Add the values in several cells with Worksheet Function.Sum

Question

I want to add 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 I get

'compile error: wrong number of arguments or invalid property assignments'

'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"))

Answer 1

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

EDIT:

• This should be a more useful approach. The number of the resulting cells depends on the last non-empty cell in column W. Keep in mind that as more accurate the details in your question, the better the answers.

Sub SumWithOffsetLastRowDependent()
    
    Const sfRow As Long = 23
    Const sCol As String = "W"
    Const srOffset As Long = 54
    
    Dim slRow As Long
    slRow = Sheet1.Cells(Sheet1.Rows.Count, sCol).End(xlUp).Row
    If slRow < sfRow Then Exit Sub ' no data
    
    Dim srg As Range
    Dim sr As Long
    
    For sr = sfRow To slRow Step srOffset
        ' Combine the cells into a range ('srg')
        If srg Is Nothing Then ' the first time
            Set srg = Sheet1.Cells(sr, sCol)
        Else ' every other time
            Set srg = Union(srg, Sheet1.Cells(sr, sCol))
        End If
    Next sr
    
    SMRY.Range("AE8").Value = Application.Sum(srg)
     
End Sub