使用工作表Function.Sum在几个单元格中添加值

Question

我想在工作表中的几个单元格中添加这些值。

它们被分组为6行，每个行有6个范围(下面的代码中没有这样做)，以便更容易地对行进行编辑，但我得到

‘编译错误:错误的参数数或无效的属性分配’

'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

行偏移和

，

• ，看上去应该有一个模式，即每54个细胞。当写这么多地址时，可能会出现错误，例如，W239发生了两次，有些偏移为52和56。
• ，您可以使用循环中的Union将所有单元格组合成一个范围，然后一次将范围进行汇总。

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

编辑：

• ，这应该是一种更有用的方法。结果单元格的数量取决于列W中最后一个非空单元格。记住，问题中的细节越准确，答案就越好。--

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