excel - 运行时错误 6 : Overflow: Excel VBA

Question

我一直在尝试解决这个问题，但未能成功。我有以下 VBA 代码。运行时出现“运行时错误 6:溢出”。我想我已经为每个变量分配了正确的类型。请帮忙!!

Sub UseCoeff()
Dim a, b As Long
Dim Value1 As Double

ThisWorkbook.Sheets("UseTableBEA").Activate
For b = 2 To 427
    For a = 2 To 431

        Value1 = ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value
        ThisWorkbook.Sheets("UseCoeff").Cells(a, b).Value = Value1

    Next a
Next b
End Sub

每个单元格的结果可能介于 0 和 1 之间，最多可达小数点后八位。提前致谢!!

Answer 1

如果 ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value 为 0，则 ThisWorkbook.Sheets("UseTableBEA").Cells(a, b)如果 ThisWorkbook.Sheets("UseTableBEA").Cells(a, b)，则 ).Value/ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value 将导致溢出错误。值也是0。

为了避免这种情况，请使用 if 语句过滤掉 0，只需用您想要的实际逻辑更新注释即可:

If ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value = 0 Then
    Value1 = 'Whatever you want but probably 0.
Else
    Value1 = ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value
End If