Excel VBA -公式在电子表格中添加数据后中断

I have an Excel spreadsheet with monthly costs for Jan. 2016 through Dec. 2016 (columns M through X). There are also two buttons on the sheet. When you click the "Perform Analysis" button, it reduces each monthly cost by the percentage entered in column D (i.e. if you enter 15 in column D, columns M (January) through X (December) are reduced by 15%).

我有一个Excel电子表格，从2016年1月到2016年12月的月费用(列M到X)，表格上还有两个按钮。当您点击“执行分析”按钮时，它将按D列中输入的百分比减少每个月的成本(即如果您在D列中输入15个，那么M列(1月)到X列(12月)将减少15%)。

Once columns M through X are reduced, then columns Z (Jan. 2017) through AK (Dec. 2017) are populated. To determine the value of columns Z through AK, I see if the average monthly cost for 2016 is greater than or less than the difference between the original total yearly cost for 2016 (column Y) and the newly reduced cost. If the average cost is greater than the difference, then Jan. 2017 (column Z) is equal to the difference between the original cost and reduced cost. If the average is less than the difference, then Jan. 2017 is equal to the average monthly cost for 2016.

一旦减少了M到X列，则通过AK(2017年1月)填充Z列(2017年12月)。通过AK来确定Z列的值，我知道2016年的平均月成本是否大于或小于2016年(列Y)和新降低成本之间的差额。如果平均成本大于差异，则2017年1月(Z栏)等于原成本与降低成本的差额。如果平均值小于差额，那么2017年1月等于2016年的月平均成本。

Next, we use the same logic as above to determine the value of column AA (Feb. 2017). However, this time we are subtracting the value of Jan. 2017 from the difference between the original yearly cost for 2016 and the newly reduced cost.

接下来，我们使用与上面相同的逻辑来确定列AA的值(2017年2月)。但这一次，我们将从2016年原年度成本与新降低的成本之间的差额中减去2017年1月的价值。

For example, if the original yearly cost for 2016 was $350,000 and the reduced cost was $297,500 (reduced by 15%), and the average monthly cost was $45,000 (also the cost for Jan. 2017), then we would calculate the value for Feb. 2017 by saying:

例如，如果2016年的原始年成本是35万美元，减少成本是297500美元(减少15%)，平均月成本是4.5万美元(也是2017年1月的成本)，那么我们计算2017年2月的价值时，我们会说:

If average monthly cost ($45,000) greater than ($350,000 - $297,500 - $45,000) Then Feb. 2017 = $275,300

如果月平均成本(4.5万美元)大于(35万至29.75万至4.5万美元)，那么2017年2月= 275,300美元

Else average monthly cost ($45,000) less than ($350,000 - $297,500 - $45,000) Then Feb. 2017 = $45,000

否则，平均月成本(4.5万美元)低于(35万至29.75万至4.5万美元)，那么2017年2月= 4.5万美元

This pattern continues through Dec. 2017, column AK.

这一模式将持续到2017年12月。

All of my code was working great, until I added numbers to Row 5 in my spreadsheet. After I added the data, my COUNTIFS formula no longer returns the correct result.

我所有的代码都运行得很好，直到我在我的电子表格中添加了数字5。在我添加数据之后，我的COUNTIFS公式不再返回正确的结果。

For Row 5, MonthsWithValues should equal 10; however, each time I run the code now, it gives me a 0. I have tried debugging it using Step Into, Break Points/Toggle Points, and Watch. Each time I run the code, it keeps saying the value of MonthsWithValues is 0. Because my code contains a formula where you divide by MonthsWithValues, I keep getting Run-time Error 11 (you can't divide by 0). Help! The relevant code is:

对于第5行，月值应该等于10;但是，每次我运行代码时，都会得到0。我尝试过使用Step Into、Break point /Toggle point和Watch调试它。每次我运行代码时，它总是说MonthsWithValues是0。因为我的代码包含一个公式，在这个公式中，你可以用一个月的值除以一个值，我一直得到运行时的错误11(你不能除以0)。相关的代码是:

Function ReduceCost_Percentage()

With Worksheets("Analysis Worksheet")

Dim i As Long
Dim LastRow As Long
LastRow = Range("X" & Rows.Count).End(xlUp).Row
Dim Orig2016Total(65) As Long
Dim MonthsWithValues As Long
Dim TodayDate As Date
TodayDate = Format(Date, "DD/MM/YYYY")

For i = 5 To LastRow
Orig2016Total(i) = Excel.WorksheetFunction.Sum(.Range(("BC" & i), ("BN" & i)))
MonthsWithValues = Excel.WorksheetFunction.CountIfs(.Range(("M" & i), ("X" & i)), "<>0", "<>")
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) 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 <= #12/31/2016# Then
    If (((Orig2016Total(i) - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues)) < (Orig2016Total(i) - .Range("Y" & i).Value) Then
      .Range("Z" & i).Value = ((Orig2016Total(i) - (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
    End If
  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 <= #12/31/2016# Then
    If (((Orig2016Total(i) - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues)) > (Orig2016Total(i) - .Range("Y" & i).Value) Then
      .Range("Z" & i).Value = (Orig2016Total(i) - .Range("Y" & i).Value) + Worksheets("Fixed Cost Test Data").Range("B" & i).Value
    End If
  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 > #12/31/2016# _
  And (Orig2016Total(i) / MonthsWithValues) < (Orig2016Total(i) - .Range("Y" & i).Value) Then
    .Range("Z" & i).Value = Orig2016Total(i) / MonthsWithValues
  ElseIf .Range("X" & i).Value > 0 And IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) _
  And (Orig2016Total(i) / MonthsWithValues) < (Orig2016Total(i) - .Range("Y" & i).Value) Then
    .Range("Z" & i).Value = Orig2016Total(i) / MonthsWithValues
  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 > #12/31/2016# _
  And (Orig2016Total(i) / MonthsWithValues) > (Orig2016Total(i) - .Range("Y" & i).Value) Then
    .Range("Z" & i).Value = Orig2016Total(i) - .Range("Y" & i).Value
  ElseIf .Range("X" & i).Value > 0 And IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value) _
  And (Orig2016Total(i) / MonthsWithValues) > (Orig2016Total(i) - .Range("Y" & i).Value) Then
    .Range("Z" & i).Value = Orig2016Total(i) - .Range("Y" & i).Value
  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) And Orig2016Total(i) > 0 Then
    If (((Orig2016Total(i) - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues)) < (Orig2016Total(i) - .Range("Y" & i).Value) Then
      .Range("Z" & i).Value = ((Orig2016Total(i) - (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
    End If
  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) And Orig2016Total(i) > 0 Then
    If (((Orig2016Total(i) - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues)) > (Orig2016Total(i) - .Range("Y" & i).Value) Then
      .Range("Z" & i).Value = (Orig2016Total(i) - .Range("Y" & i).Value) + Worksheets("Fixed Cost Test Data").Range("B" & i).Value
    End If
  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 Worksheets("Fixed Cost Test Data").Range("C" & i).Value > #11/30/2016# And Worksheets("Fixed Cost Test Data").Range("C" & i).Value <= #12/31/2016# Then
    If ((Orig2016Total(i) - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues) < (Orig2016Total(i) - .Range("Y" & i).Value) Then
     .Range("Z" & i).Value = ((Orig2016Total(i) - (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
    End If
  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 Worksheets("Fixed Cost Test Data").Range("C" & i).Value > #11/30/2016# And Worksheets("Fixed Cost Test Data").Range("C" & i).Value <= #12/31/2016# Then
    If ((Orig2016Total(i) - (Worksheets("Fixed Cost Test Data").Range("B" & i).Value * (12 - Left(Worksheets("Fixed Cost Test Data").Range("C" & i).Value, 2)))) / MonthsWithValues) > (Orig2016Total(i) - .Range("Y" & i).Value) Then
     .Range("Z" & i).Value = (Orig2016Total(i) - .Range("Y" & i).Value) + Worksheets("Fixed Cost Test Data").Range("B" & i).Value
    End If
  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 Worksheets("Fixed Cost Test Data").Range("C" & i).Value > #12/31/2016#) Or IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value)) _
  And (Orig2016Total(i) / MonthsWithValues) > (Orig2016Total(i) - .Range("Y" & i).Value) Then
    .Range("Z" & i).Value = Orig2016Total(i) - .Range("Y" & 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 Worksheets("Fixed Cost Test Data").Range("C" & i).Value > #12/31/2016#) Or IsEmpty(Worksheets("Fixed Cost Test Data").Range("B" & i).Value)) _
  And (Orig2016Total(i) / MonthsWithValues) < (Orig2016Total(i) - .Range("Y" & i).Value) Then
    .Range("Z" & i).Value = Orig2016Total(i) / MonthsWithValues
  End If
End If
Next i

1 个解决方案

#1

It was giving me the error because MonthsWithValues wasn't being initialized with a number, so it was being evaluated to 0.

它给了我错误，因为MonthsWithValues没有被数字初始化，所以它被赋值为0。

The correct code is:

正确的代码是:

Sub ReduceCost_Percentage()

With Worksheets("Analysis Worksheet")

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

For i = 5 To LastRow
Orig2016Total(i) = Excel.WorksheetFunction.Sum(.Range(("BC" & i), ("BN" & i)))
MonthsWithValues(i) = Excel.WorksheetFunction.CountIfs(.Range(("M" & i), ("X" & i)), "<>0", .Range(("M" & i), ("X" & i)), "<>""""")
Next i

Now it works! Thanks for the help everyone!

现在它工作!谢谢大家的帮助!