使用工作表函数将多个单元格中的值相加.总和

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