除以零时的 VBA 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18598872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA script when dividing by zero
提问by Hyman Prible
I am trying to do a formula in VBA, but I am coming across errors because of a zero in the denominator. If I have a zero in the denominator, I'd like the active cell to be set to zero. Whatever I am doing is incorrect, and I am not a programmer. I have no idea what I'm doing. Any help would be greatly appreciated. Here is what I
我正在尝试在 VBA 中做一个公式，但由于分母为零，我遇到了错误。如果分母为零，我希望将活动单元格设置为零。无论我做什么都是不正确的，而且我不是程序员。我不知道我在做什么。任何帮助将不胜感激。这是我
Range("H" & Row).Activate
If Range("F" & Row) = 0 Then ActiveCell.Formula = 0
ActiveCell.Formula = Range("G" & Row) / Range("F" & Row)
回答by Adriaan Stander
try using an else
尝试使用其他
something like
就像是
Range("H" & Row).Activate
If Range("F" & Row) = 0 Then
ActiveCell.Formula = 0
Else
ActiveCell.Formula = Range("G" & Row) / Range("F" & Row)
End If
回答by tigeravatar
You could just build the statement into the formula:
您可以将语句构建到公式中：
For Row = 1 To 3
Cells(Row, "H").Formula = "=IF(F" & Row & "=0,0,G" & Row & "/F" & Row & ")"
Next Row
And instead of using a loop...
而不是使用循环......
With Range("H2:H" & Cells(Rows.Count, "G").End(xlUp).Row)
.Formula = "=IF(F" & .Row & "=0,0,G" & .Row & "/F" & .Row & ")"
.Value = .Value
End With
回答by Constablebrew
I do not recommend Cor_Blimey or tigeravatar recommend - both make the code less readable. Cor_Blimey's suggestion to use the line continuation character " _" and the end of statement character ":" do not add to the succinctness of the code and is logically the same as the code above. Tigeravatar is sortof on the right track with suggesting that the cell handle the error case itself, but his code suggestion makes everything more difficult to read.
我不推荐 Cor_Blimey 或 Tigeravatar 推荐 - 两者都会降低代码的可读性。Cor_Blimey 建议使用行继续符“_”和语句结束符“:”不会增加代码的简洁性，在逻辑上与上面的代码相同。Tigeravatar 的建议在正确的轨道上，建议单元本身处理错误情况，但他的代码建议使所有内容更难以阅读。
My suggested change is to drop the VBA entirely and use the following formula in the cell(s) in column H that would be doing the calculations:
我建议的更改是完全删除 VBA，并在 H 列的单元格中使用以下公式进行计算：
=IfError(G1/F1,0)
This would work because you are not using any values in your calculations other than what is already on the worksheet.
这会起作用，因为除了工作表上已有的值之外，您在计算中没有使用任何值。
