通过VBA在每个空间范围之间添加求和公式 [英] Adding sum formula between every space ranges by vba

问题描述

我试图做一个自动化操作，我被困在这里，我需要在空间范围之间动态添加求和公式.我在这里完全迷失了使用VBA添加公式的方法，任何人都可以帮助我.

I was trying to do an automation and i was stuck here, where i need to add sum formula dynamically in between the space ranges. I'm completely lost here for adding formula using VBA can anyone help me out.

预先感谢您:)

推荐答案

我假设如果单元格中有一个空白，则希望将所有其他元素加起来并将结果放在该空白中，这就是你想要的.可能有很多方法可以编码，但这是我的尝试

I'm assuming what you want is if there is a blank in a cell, you want all of the other elements summed and the result placed in that blank. There are probably any number of ways to code this, but here is my attempt

Sub formulateSubtotals()
finalRow = Cells(Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row
finalCol = Cells(1, Worksheets("Sheet1").Columns.Count).End(xlToLeft).Column
For j = 1 To finalCol
    For i = finalRow + 1 To 1 Step -1
        If IsEmpty(Cells(i, j)) Then
            If IsEmpty(Cells(i - 2, j)) Then
                firstRow = i - 1
                lastRow = firstRow
            Else
                lastRow = i - 1
                firstRow = Cells(i - 1, j).End(xlUp).Row
            End If
            Cells(i, j) = Application.WorksheetFunction.Sum(Range(Cells(firstRow, j), Cells(lastRow, j)))
        End If
    Next i
Next j
End Sub

这还假定所讨论的工作表的标题为"Sheet1".

This also assumes that the sheet in question is entitled "Sheet1".

这篇关于通过VBA在每个空间范围之间添加求和公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！