Excel VBA - Расчет возвращает сумму для последней строки вместо первой строки

Question

Я пытаюсь настроить вычисление, которое суммирует все значения между "M" и "X" в каждой строке. Однако, когда я запускаю код, он дает мне только сумму для последней строки на рабочем листе, когда он должен возвращать сумму для 5-й строки на рабочем листе.

Конкретный блок у меня проблемы с написанием правильно:

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 для суммы 5-й строки, но вместо этого оно возвращает сумму последней строки в размере 1 144 669.

Может кто-нибудь помочь мне понять, почему я получаю сумму за последний ряд, когда она должна отображать сумму за 5-й ряд? Спасибо!!

Весь код:

    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

Answer 1

Пока вы перебираете строки 5 ...LastRow в первом цикле, вычисляющем сумму ячеек перед изменением их содержимого, сохраняет каждую строку в одной и той же скалярной переменной, которая может содержать только одно значение. Он перезаписывается в каждой итерации.

Вместо этого создайте Orig2016Total как массив размером LastRow чтобы вы могли поместить каждую строку для строки i в Orig2016Total(i) . Затем используйте i-й элемент массива в следующих циклах.