我的完整代码如下：
FirstRow = Columns("B").Find("B").MergeArea.Row
LastRow = Columns("B").Find("B").MergeArea.Row + Columns("B").Find("B").MergeArea.Rows.Count - 1
FirstRow2 = Columns("B").Find("N").MergeArea.Row
LastRow2 = Columns("B").Find("N").MergeArea.Row + Columns("B").Find("N").MergeArea.Rows.Count - 1
Range("A" & LastRow + 1).EntireRow.Insert
Range("A" & LastRow2 + 2).EntireRow.Insert
Range("Q" & LastRow + 1) = 50
Range("Q" & LastRow2 + 2) = 100
Dim Col, ColArr, CalcRow, CalcRowArr, FRow, FirstRowArr, LRow, LastRowArr
ColArr = Array("R", "W", "Y")
CalcRowArr = Array(LastRow + 1, LastRow2 + 2)
FirstRowArr = Array(FirstRow, FirstRow2 + 1)
LastRowArr = Array(LastRow, LastRow2 + 1)
For Each Col In ColArr
For Each CalcRow In CalcRowArr
For Each FRow In FirstRowArr
For Each LRow In LastRowArr
Range(Col & CalcRow).Formula = "=SUM(" & Col & FRow & ":" & Col & LRow & ")"
Next LRow
Next FRow
Next CalcRow
Next Col
基本上我正在尝试转换这 6 行代码：
Range("R" & LastRow + 1).Formula = "=SUM(R" & FirstRow & ":R" & LastRow & ")"
Range("W" & LastRow + 1).Formula = "=SUM(W" & FirstRow & ":W" & LastRow & ")"
Range("Y" & LastRow + 1).Formula = "=SUM(Y" & FirstRow & ":Y" & LastRow & ")"
Range("R" & LastRow2 + 2).Formula = "=SUM(R" & FirstRow2 + 1 & ":R" & LastRow2 + 1 & ")"
Range("W" & LastRow2 + 2).Formula = "=SUM(W" & FirstRow2 + 1 & ":W" & LastRow2 + 1 & ")"
Range("Y" & LastRow2 + 2).Formula = "=SUM(Y" & FirstRow2 + 1 & ":Y" & LastRow2 + 1 & ")"
进入我上面代码中的数组循环：
Dim Col, ColArr, CalcRow, CalcRowArr, FRow, FirstRowArr, LRow, LastRowArr
ColArr = Array("R", "W", "Y")
CalcRowArr = Array(LastRow + 1, LastRow2 + 2)
FirstRowArr = Array(FirstRow, FirstRow2 + 1)
LastRowArr = Array(LastRow, LastRow2 + 1)
For Each Col In ColArr
For Each CalcRow In CalcRowArr
For Each FRow In FirstRowArr
For Each LRow In LastRowArr
Range(Col & CalcRow).Formula = "=SUM(" & Col & FRow & ":" & Col & LRow & ")"
Next LRow
Next FRow
Next CalcRow
Next Col
但是，我的最终结果是分别跳过 FirstRowArr 和 LastRowArr 中的第一项（FirstRow 和 LastRow），这意味着我的第一组 3 个值（“R”、“W”、“Y”和 LastRow + 1）成为重复我的第二组 3 个值（“R”、“W”、“Y”和 LastRow2 + 2）。
根据我之前的评论，您只需要两个循环：
For Each Col In ColArr
Dim n as long
For n = lbound(calcrowarr) to ubound(calcrowarr)
range(col & calcrowarr(n)).formula = "=SUM(" & Col & FirstRowArr(n) & ":" & Col & LastRowArr(n) & ")"
next n
Next Col
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句