32000行后，VBA宏崩溃 [英] VBA Macro crashes after 32000 rows

问题描述

我有一个VBA宏，根据在3列中的单元格中查找值，将行从一个工作表复制到另一个工作表。宏工作，但到达第32767行时崩溃。此行中没有公式或特殊格式。此外，我已经把该行，但它仍然崩溃的行号。这是excel中的限制吗？在工作表中有大约43000正在处理

I have a VBA macro that copies rows from one worksheet into another based upon finding values in cells in 3 columns. The macro works, but crashes when it reaches row 32767. There are no formulas or special formatting in this row. Further, I have taken that row out, but it still crashes on that row number. Is this a limitation in excel? There are some 43000 in the worksheet that is being process

因此，我问我的宏有什么问题，以及如何让它到达工作表的末尾： / p>

Therefore, I ask what is wrong with my macro and how I can get it reach the end of the worksheet:

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim wks As Worksheet
On Error GoTo Err_Execute

对于工作表中的每个wks

For Each wks In Worksheets

LSearchRow = 4
LCopyToRow = 4

ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
Set wksCopyTo = ActiveSheet
wks.Rows(3).EntireRow.Copy wksCopyTo.Rows(3)

While Len(wks.Range("A" & CStr(LSearchRow)).Value) > 0

    If wks.Range("AB" & CStr(LSearchRow)).Value = "Yes" And wks.Range("AK" & CStr(LSearchRow)).Value = "Yes" And wks.Range("BB" & CStr(LSearchRow)).Value = "Y" Then

        Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
        Selection.Copy


        wksCopyTo.Select
        wksCopyTo.Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
        wksCopyTo.Paste

        'Move counter to next row
        LCopyToRow = LCopyToRow + 1
        'Go back to Sheet1 to continue searching
        wks.Select
    End If
    LSearchRow = LSearchRow + 1
Wend

Application.CutCopyMode = False
Range("A3").Select
MsgBox "All matching data has been copied."
Next wks
    Exit Sub
Err_Execute:
    MsgBox "An error occurred."

请帮忙！

推荐答案

VBAInt类型是一个有符号的16位字段，因此它只能保存从-32768到+32767的值。将这些变量更改为Long，这是一个有符号的32位字段，可以保存从-2147483648到+2147483647的值。应该足够Excel。 ;）

The VBA 'Int' type is a signed 16-bit field so it can only hold values from -32768 to +32767. Change those variables to 'Long', which is a signed 32-bit field and can hold values from -2147483648 to [phone]. Should be enough for Excel. ;)

这篇关于32000行后，VBA宏崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！