Office 2007至2010更新后的VBA溢出错误 [英] VBA Overflow error after office 2007 to 2010 update

问题描述

我有一个VBA宏，可以在Excel 2007中正常工作，但是在更新到2010后，它会引发错误.宏基本上将原始数据从一张纸复制到多张纸.引发的错误是错误6:溢出.引发错误的行是Dim

I have a VBA macro that worked correctly in Excel 2007 but after updating to 2010 it throws errors. The Macro basically copies raw data from one sheet to multiple sheets. The error being thrown is error 6: Overflow. The line that is throwing the error is Dim

y As Integer
y = Worksheets("Raw Data").Range("A2").End(xlDown).Row

我最初以为可以将其更改为long，这样可以消除溢出错误.好吧，我猜它杀死了该错误，但它也输出了非常错误的结果，然后认为该溢出错误甚至没有任何意义……只有973行.

I initially thought ok ill change it to long and it will kill the overflow error. Well i guess it killed the error but it also output very wrong results and then relized that the overflow error doesnt even make sense... there is only 973 rows.

然后我认为可以尝试试试吧

I then thought ok maybe ill try this instead

Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select

现在，它在随后的行上引发对象'_global'的运行时错误'1004'方法'范围'失败".

Now it throws "Run-time error '1004' method 'range' of object '_global' failed" on the line after.

是完整代码的一部分. 我对它甚至可能是溢出错误感到满意吗?感谢您的帮助.

below is a portion of the full code. I am usure of how it could even be a overflow error? Any help is appreciated.

Dim y As Integer
'y = Worksheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
y = Worksheets("Raw Data").Range("A2").End(xlDown).Row

Range("B1:U" & y).Select
Selection.Copy
Sheets("Yellow Suppliers").Select
Range("B2").Select
ActiveSheet.Paste
Columns("C:E").Select
Selection.Delete Shift:=xlToLeft
Columns("P:Q").Select
Selection.Delete Shift:=xlToLeft

Columns("A").ColumnWidth = 2.14
Columns("B").ColumnWidth = 43.43
Columns("C").ColumnWidth = 12.14
Columns("D:O").ColumnWidth = 8
Columns("P").ColumnWidth = 10.14

Rows("1").RowHeight = 15
Rows("2:" & y).RowHeight = 30

Range("B3:B22").Select
Selection.Font.Bold = True

推荐答案

尝试摆脱代码中的任何Select或Selection.另外，请确认y的确能达到973的期望值.

Try getting rid of any Select or Selection in your code. Also, confirm that y indeed does evaluate to the expected value of 973.

Sub Macro2()
Dim rngY as Range '## a variable to calculate the size of rows used in Column A'
Dim y As Integer

With Worksheets("Raw Data")
    Set rng = .Range("A2").Resize( _
        Application.WorksheetFunction.CountA(.Range("A:A")))
End With
    y = rngY(rngY.Rows.Count).Row

    Range("B1:U" & y).Copy Destination:= _
        Sheets("Yellow Suppliers").Range("B2")

    Columns("C:E").Delete Shift:=xlToLeft
    Columns("P:Q").Delete Shift:=xlToLeft

    Columns("A").ColumnWidth = 2.14
    Columns("B").ColumnWidth = 43.43
    Columns("C").ColumnWidth = 12.14
    Columns("D:O").ColumnWidth = 8
    Columns("P").ColumnWidth = 10.14

    Rows("1").RowHeight = 15
    Rows("2:" & y).RowHeight = 30

    Range("B3:B22").Font.Bold = True
End Sub

这篇关于Office 2007至2010更新后的VBA溢出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！