Excel VBA - 计算返回最后一行而不是第一行的总和

2024-6-17 • tag-icon

我正在尝试设置一个计算，将每行中“M”和“X”之间的所有值相加。但是，当我运行代码时，它只返回工作表中最后一行的总和，而它应该返回工作表中第五行的总和。

我无法正确书写的特定块是：

For i = 5 To LastRow
  If Not IsEmpty(.Range(.Cells(i, 13), .Cells(i, 24))) Then
Orig2016Total = .Range("M" & i).Value + .Range("N" & i).Value + .Range("O" & i).Value + .Range("P" & i).Value _
+ .Range("Q" & i).Value + .Range("R" & i).Value + .Range("S" & i).Value + .Range("T" & i).Value _
+ .Range("U" & i).Value + .Range("V" & i).Value + .Range("W" & i).Value + .Range("X" & i).Value
  End If
Next I

下面代码块中加粗的部分.Range("Z" & i).Value = Orig2016Total应该返回第五行的总和 780,000，但它却返回了最后一行的总和 1,144,669。

有人能帮我弄清楚为什么我应该显示第五行的总和，但却得到了最后一行的总和吗？谢谢！！

整个代码如下：

    Function ReduceCost_Percentage()

    With Worksheets("Analysis Worksheet")

    Dim i As Long
    Dim LastRow As Long
    LastRow = Range("X" & Rows.Count).End(xlUp).Row

    Dim TodayDate As Date
    TodayDate = Format(Date, "DD/MM/YYYY")

    Dim Orig2016Total As Long
    Dim MonthsWithValues As Long


   For i = 5 To LastRow
      If Not IsEmpty(.Range(.Cells(i, 13), .Cells(i, 24))) Then
        Orig2016Total = .Range("M" & i).Value + .Range("N" & i).Value + .Range("O" & i).Value + .Range("P" & i).Value _
        + .Range("Q" & i).Value + .Range("R" & i).Value + .Range("S" & i).Value + .Range("T" & i).Value _
        + .Range("U" & i).Value + .Range("V" & i).Value + .Range("W" & i).Value + .Range("X" & i).Value
     End If
    Next i


    For i = 5 To LastRow
     If .Range("D" & i).Value > 0 And IsEmpty(.Range("B" & i).Value) _
     And IsEmpty(.Range("C" & i).Value) And Not IsEmpty(.Range("M" & i).Value) _
     And Not IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) _
     And ((TodayDate >= Worksheets("Fixed Cost Test Data").Range("C" & i).Value And Worksheets("Fixed Cost Test Data").Range("C" & i).Value <= #12/31/2015#) Or Worksheets("Fixed Cost Test Data").Range("C" & i).Value <= #12/31/2015#) Then
      .Range("M" & i).Value = ((.Range("M" & i).Value - Worksheets("Fixed Cost Test Data").Range("B" & i).Value) - ((.Range("M" & i).Value - Worksheets("Fixed Cost Test Data").Range("B" & i).Value) * (.Range("D" & i).Value * 0.01))) + Worksheets("Fixed Cost Test Data").Range("B" & i).Value
    ElseIf .Range("D" & i).Value > 0 And IsEmpty(.Range("B" & i).Value) _
    And IsEmpty(.Range("C" & i).Value) And Not IsEmpty(.Range("M" & i).Value) _
    And (IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) Or Worksheets("Fixed Cost Test Data").Range("C" & i).Value > TodayDate Or Worksheets("Fixed Cost Test Data").Range("C" & i).Value > #12/31/2015#) Then
     .Range("M" & i).Value = .Range("M" & i).Value - (.Range("M" & i).Value * (.Range("D" & i).Value * 0.01))
    End If
   Next I

    'The code continues the same for columns "N" through "X" then it picks up again as:

    For i = 5 To LastRow

    .Range("Y" & i).Formula = "=SUM(" & .Range(Cells(i, 13), Cells(i, 24)).Address(False, False) & ")"

    MonthsWithValues = Application.WorksheetFunction.CountIfs(Worksheets("Analysis Worksheet").Range(.Cells(i, 13), .Cells(i, 24)), "<>0", Worksheets("Analysis Worksheet").Range(.Cells(i, 13), .Cells(i, 24)), "<>""")

     If .Range("D" & i).Value > 0 And IsEmpty(.Range("B" & i).Value) And IsEmpty(.Range("C" & i).Value) Then
       If .Range("X" & i).Value > 0 And Not IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) _
       And Worksheets("Fixed Cost Test Data").Range("C" & i).Value <= #11/30/2016# Then
        ***.Range("Z" & i).Value = Orig2016Total***
      ElseIf .Range("X" & i).Value > 0 And Not IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) _
      And Worksheets("Fixed Cost Test Data").Range("C" & i).Value > #11/30/2016# Then
       .Range("Z" & i).Value = (Orig2016Total - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues
      ElseIf .Range("X" & i).Value > 0 And IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) Then
       .Range("Z" & i).Value = Orig2016Total / MonthsWithValues
      ElseIf .Range("X" & i).Value = Worksheets("Fixed Cost Test Data").Range("B" & i).Value And Not IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) _
      And Not IsEmpty(Worksheets("Fixed Cost Test Data").Range("C" & i).Value) Then
       .Range("Z" & i).Value = ((Orig2016Total - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues) + Worksheets("Fixed Cost Test Data").Range("B" & i).Value
     ElseIf (IsEmpty(.Range("X" & i).Value) Or .Range("X" & i).Value = 0) And Not IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) _
     And Not IsEmpty(Worksheets("Fixed Cost Test Data").Range("C" & i).Value) Then
       .Range("Z" & i).Value = (Orig2016Total - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues
     ElseIf (IsEmpty(.Range("X" & i).Value) Or .Range("X" & i).Value = 0) And IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) Then
      .Range("Z" & i).Value = Orig2016Total / MonthsWithValues
     End If
   End If
  Next i


   End With
   End Function

答案1

当您在第一个循环中循环第 5...LastRow 行，在更改单元格内容之前计算单元格的总和时，您会将每个行总和保存在同一个标量变量中，该变量只能保存一个值。它会在每次迭代中被覆盖。

相反，创建Orig2016Total一个大小为的数组LastRow，以便您可以将每行的行数总和i放入Orig2016Total(i)。然后在以下循环中使用第 i 个数组元素。

答案1

相关内容