如何对这种同一列的不连续单元格进行求和,空值定位求和的数据都是在下面合计的,我想在上面合计怎么做呢?
用VBA,选好数据,自动填写求和公式。
代码
Sub 空格求和()
Dim mr As Range, m()
Set mr = Selection
n = mr.Count
ReDim m(1 To n)
For i = 1 To n
If mr.Cells(i).Text = "" Then
j = j + 1
m(j) = i
End If
Next i
For i = 1 To j
If i = j Then
mr.Cells(m(i)).FormulaR1C1 = "=SUM(R[1]C:R[" & n - m(i) & "]C)"
mr.Cells(m(i)).Font.Color = -16776961
Else
mr.Cells(m(i)).FormulaR1C1 = "=SUM(R[1]C:R[" & m(i + 1) - m(i) - 1 & "]C)"
mr.Cells(m(i)).Font.Color = -16776961
End If
Next i
End Sub