VBA循环有条件停止在任意数量的行之后被评估

Question

这是我的代码。代码本身似乎工作，但由于某种原因，条件语句在第32645行后停止。我尝试将所有变量切换为Long，并没有帮助。

另外，如果我使代码在第32646行开始，但随后在随后的一行（〜18000）处停止，代码将起作用。它所停止的数据似乎没有相似之处。最初我尝试了一个指定开始和结束行的for循环，但是这也不起作用，所以我尝试了while循环（理论上都应该可以，但似乎都不行）。

它需要能够处理130,000 +行，任何想法为什么这可能会发生？

的问题

回路没有错误，并通过添加一个消息框停止运行，我知道我行增加变量在最后一行结束了，但根据工作簿时，条件语句停止在后评估任意数量的行。

如果我从第一次运行停止的下一行开始运行脚本，它可以工作，但同样适用于（可能不同）任意数量的步骤。

注

我已经做了我所有的变量 “龙” 型。我使用Option Explicit。

data_row = CLng(InputBox("Please enter the row number of the first data entry")) 
Worksheets.Add(After:=Worksheets(1)).name = "Formatted_Output" 

Set ws = Sheets("Formatted_Output") 
Worksheets(1).Activate 
LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column 
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).row 
data_col = LastCol 

'loop through entries to get account and permissions 
increment = data_row 
Do Until increment = LastRow 
    If Cells(data_row, data_col) = "" Then 
     data_col = data_col - 1 
    Else 
    ' ~> For illegal ==> access denied permission that throws errors 
     If Cells(data_row, data_col).Value = "==>access denied" Then 
      permissions = "access denied" 'remove illegal = 
      ws.Cells(output_row, 3).Value = permissions 'print permissions to output file 
     Else 
      permissions = Cells(data_row, data_col).Value 'cell should be permission cell of each row 
      ws.Cells(output_row, 3).Value = permissions 'print permissions to output file 
     End If 

     data_col = data_col - 1 'domain/account cell is now highlighted 

     If InStrRev(Cells(data_row, data_col).Value, "?") > 0 Then 

      account = Split(Cells(data_row, data_col).Value, "?")(1) & Str(unknown_count) ' separate domain name and unknown id 
      unknown_count = unknown_count + 1 ' counting the number of unkown accounts found 
      ws.Cells(output_row, 2) = account 'print unknown account Id to output 

      domain_bit = Split(Cells(data_row, data_col).Value, "?")(0) '' get separate domain name from id cell 
      data_col = data_col - 1 'domain second from end cell is now highlighted 

      Do While data_col > 0 'generate domain from rest of row 
       domain_bit = Cells(data_row, data_col).Value & domain_bit 'domain built backwards 
       data_col = data_col - 1 'check next column for more of location name 
      Loop 

      ws.Cells(output_row, 1) = domain_bit 
       'data_col = LastCol 
       'data_row = data_row + 1 
       'output_row = output_row + 1 

     ElseIf InStrRev(Cells(data_row, data_col).Value, "\") > 0 Then 

      account = Split(Cells(data_row, data_col).Value, "\")(1) 'separate account ID 
      ws.Cells(output_row, 2) = account 'print account ID to oputput 

      domain_bit = Split(Cells(data_row, data_col).Value, "\")(0) 
      data_col = data_col - 1 'domain second from end cell is now highlighted 

      Do While data_col > 0 'generate domain from rest of row 
       domain_bit = Cells(data_row, data_col).Value & domain_bit 'domain built backwards 
       data_col = data_col - 1 'check next column for more of location name 
      Loop 

      ws.Cells(output_row, 1) = domain_bit 'output to file 

     Else 
         account = Cells(data_row, data_col).Value 'account is just whole cell whether empty or one word no path 
      ws.Cells(output_row, 2) = account 'print account ID to oputput 

      data_col = data_col - 1 'domain second from end cell is now highlighted (since no domain in account cell) 
      Do While data_col > 0 'generate domain from rest of row 
       domain_bit = Cells(data_row, data_col).Value & domain_bit 'domain built backwards 
       data_col = data_col - 1 'check next column for more of location name 
      Loop 

      ws.Cells(output_row, 1) = domain_bit 'output to file 
     End If 

    data_col = LastCol 
    data_row = data_row + 1 
    output_row = output_row + 1 
    End If 

'Next increment 
ws.Range("E" & 1) = unknown_count 
increment = increment + 1 

If increment = LastRow Then 
    MsgBox (Str(increment) & "=" & Str(LastRow)) 
End If 

Loop 

Answer 1

我相信你的第一个首要if语句不这样做，因为它应该，这是如果它被发现是真data_row变量没有增加禁用自动排增加。

如果这是你想要的，那么increment变量需要在if语句内移动，因为它会随着变量的增加而变化，而不考虑data_row变量。

我觉得for-next会更好：

for data_row = 1 to LastRow 
    'do some stuff 
next 

这将循环中的所有行1至LastRow每次迭代。

---

添加更好的答案：

Do Until increment > LastRow 
    ... 
    data_col = LastCol 
    data_row = data_row + 1 
    output_row = output_row + 1 
    increment = increment + 1 
    End If 

    If increment > LastRow Then 
     MsgBox (Str(increment) & ">" & Str(LastRow)) 
    End If 
Loop