VBA - 运行时错误6.溢出

问题描述：

这是我正在尝试修复的代码。这是来自Excel电子表格宏。VBA - 运行时错误6.溢出

Ex。当我运行Tally检查器宏并键入检查器的名称时，宏将假设在每周的每一天都接收数据表单，然后填充我所做的表。但是当我运行它，我得到运行时错误6.溢出错误。

代码：

Sub Tally Inspector()  
    Dim personName As String 
    personName = InputBox("Enter Name Of Inspector") 

    Dim counter As Integer 

    Dim endOfTable 
    endOfTable = 130 

    Dim witnessSum As Integer: witnessSum = 0 'number witness by CCI 
    Dim ICbyCrew As Integer: ICbyCrew = 0 'number done by crew 

    Dim columnLetter As Integer: columnLetter = 11 'K 

    For counter = 5 To endOfTable 
     If StrComp(Cells(counter, columnLetter), personName) = 0 And _ 
      IsNumeric(Cells(counter, columnLetter - 1)) = True Then 

      witnessSum = (witnessSum + Cells(counter, columnLetter - 1).Value) 
      ICbyCrew = (ICbyCrew + Cells(counter, 4).Value) 
     End If 
    Next counter  

    For counter = 150 To 160 Step 1 
     If Cells(counter, "E").Value = personName Then 
      Cells(counter, "F").Value = ICbyCrew 
      Cells(counter, "G").Value = witnessSum 
      Cells(counter, "H").Value = (witnessSum/ICbyCrew)* 100 'This line is highlighted when I run the debugger' 

      Exit For 
     End If 
    Next counter 
End Sub  

Sub Inspector() 

    Dim Inspector As String 
    Dim Inspection As Integer: Inspection = 0 
    Dim Witness As Integer: Witness = 0 

    For x = 155 To 158  
     Inspector = Cells(x, "E") 
     If Inspector = "" Then 
      Exit For 
     End If 

     For y = 5 To 120 
      If (StrComp(Inspector, Cells(y, "K")) = 0) And (IsNumeric(Cells(y, "D")) = True) And (IsNumeric(Cells(y, "J")) = True) Then 
       Inspection = Inspection + Cells(y, "D") 
       Witness = Witness + Cells(y, "J") 
      End If     
     Next y 

    Cells(x, "F") = Inspection 
    Cells(x, "G") = Witness 
    Cells(x, "H") = (Witness/Inspection) * 100 

    Inspection = 0 
    Witness = 0   
    Next x  
End Sub

你在哪一行得到错误？ – Sam

'x'和'y'完全没有定义。其最佳做法是明确使用选项显式和定义变量类型。 –

“Cells（counter，”H“）。Value =（witnessSum/ICbyCrew）100” - Line 34 – Adeeb

答

重新定义你的Integer为Long。您可能正在运行超过Integer的32,767个数字限制。

*您可能正在运行整数32,767的数字限制* - 代码实际设置了所有定义变量的硬开始和结束点（x和y，但是没有在任何地方定义！） –

@ScottHoltzman嘿斯科特，请阅读您的评论昨天，但不是很确定你的意思 - 谨慎阐述？ – dwirony

例如，在行中：'For counter = 5 To endOfTable'用户将'endOfTable'设置为130所定义的值，该整数将处理。在循环中使用'counter'的地方也会发生这种情况。 –

答

更改高亮代码：

Cells(counter, "H").Value = (witnessSum/ICbyCrew) 100

到：

Cells(counter, "H").Value = (witnessSum/ICbyCrew)/100

或邮寄至：

Cells(counter, "H") = witnessSum/ICbyCrew & 100

或邮寄至：

Cells(counter, "H") = witnessSum/ICbyCrew & " " &100

Cells（counter，“H”）。Value =（witnessSum/ICbyCrew）* 100 – Adeeb

@Adeeb - 这个细胞究竟应该留在什么地方？ – Vityata

它只是我的电子表格中计算百分比的一列。 – Adeeb

VBA - 运行时错误6.溢出

相关推荐