使用以下 Excel 2010 VBA 出现溢出错误 6

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/12624454/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 17:53:15  来源:igfitidea点击:

Overflow Error 6 with the following Excel 2010 VBA

excelvba

提问by RCoy1978

The following code will format my template correctly the way I want. However, in the event the template is empty and a user hits the prep upload button on the sheet, I will receive an Overflow Error 6. Is there any way to remove what is causing this error?

以下代码将按照我想要的方式正确格式化我的模板。但是,如果模板为空并且用户点击工作表上的准备上传按钮,我将收到溢出错误 6。有什么方法可以消除导致此错误的原因?

    Sub PrepForUpload()

Dim cel As Range, rng As Range

Set rng = Range("A2", Range("A65536").End(xlUp))

For Each cel In rng

    If cel.Value = "" Then

        If cel.Offset(, 2).Value = "" Then
            cel.EntireRow.Delete

        End If

    End If

    Next cel

Dim rowNumber As Integer
With Sheets("Initiatives")

If Len(.Cells(2, 1)) = 0 Then

rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1

Else: rowNumber = .Cells(2, 1).End(xlDown).Row + 1

End If

.Rows(rowNumber & ":" & .Rows.Count).Clear

End With


End Sub

Debug points to the following line as the issue:

调试指向以下行作为问题:

rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1

Thanks

谢谢

Ryan

瑞安

回答by psubsee2003

You are getting an overflow because Integerin VBA is a 16 bit signed number (max value of 32767). Regardless of the version of excel, you have a minimum of 65535 rows and most likely more if you are using the XLSX file format. You need to change rowNumberto a Long

由于Integer在 VBA 中是一个 16 位有符号数(最大值为 32767),因此您会出现溢出。无论 excel 的版本如何,您至少有 65535 行,如果您使用 XLSX 文件格式,则很可能更多。您需要更改rowNumberLong

And you also have to code around the blank worksheet scenerio. When you call this line:

而且您还必须围绕空白工作表场景进行编码。当您拨打此行时:

rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1

and the worksheet is blank, .End(xlDown)will return the last possible row in the worksheet, which in the case of Excel 2010 (and Excel 2007) is 1048576. Once you change rowNumberto a Longyou will no longer get the overflow error, but you will run into a problem with this line:

并且工作表为空白,.End(xlDown)将返回工作表中最后可能的行,在 Excel 2010(和 Excel 2007)的情况下为 1048576。一旦更改rowNumber为 a,Long您将不再收到溢出错误,但您会遇到这条线的问题:

.Rows(rowNumber & ":" & .Rows.Count).Clear

This is because you are trying to select a range (row 1048577) that does not exist (hence the type mismatch). You need to add a line of code to work around this scenario. Either check initially for a blank worksheet, or check for row > 1048576.

这是因为您试图选择一个不存在的范围(第 1048577 行)(因此类型不匹配)。您需要添加一行代码来解决这种情况。最初检查空白工作表,或检查行 > 1048576。

The simplest thing to do is to just add a line to check this:

最简单的方法是添加一行来检查:

If rowNumber <= 1048576 Then
    .Rows(rowNumber & ":" & .Rows.Count).Clear
End If

回答by AMissico

Change rowNumberto Longthen add 1&or use CLngto get rid of the 'type-mismatch` error.

更改rowNumberLongthen add1&或 useCLng以消除“类型不匹配”错误。

rowNumber = CLng(.Cells(2, 1).End(xlDown).End(xlDown).Row + 1)

or

或者

rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1&

回答by nutsch

Can you show us what your data looks like in column A? You can also try this code, which test for the number of lines before attempting to get the row number

你能告诉我们你在 A 列中的数据是什么样的吗?你也可以试试这个代码,它在尝试获取行号之前测试行数

Dim cel As Range, rng As Range

If Application.WorksheetFunction.CountA(Columns(1)) <= 1 Then
    MsgBox "No lines"
    Exit Sub
End If

Set rng = Range("A2", Cells(Rows.Count, 1).End(xlUp))

For Each cel In rng

    If Len(cel.Value) = 0 Then

        If Len(cel.Offset(, 2).Value) = 0 Then cel.EntireRow.Delete

    End If

Next cel

Dim rowNumber As Long

With Sheets("Initiatives")

    If Application.WorksheetFunction.CountA(.Columns(1)) <= 1 Then
        MsgBox "No lines in sheet Initiative"
        Exit Sub
    End If


If Len(.Cells(2, 1)) = 0 Then

    rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1

    Else: rowNumber = .Cells(2, 1).End(xlDown).Row + 1

End If

.Rows(rowNumber & ":" & .Rows.Count).Clear

End With