VBA Error 6 Overflow : But where?

[jbesclapez]

Hi,

I have a macro that turns a cross table into a flat table.
The 2 first columns are repeated and should be on the flat table...
The 3rd column and after are where the values are. So i copy the range of the columsn after column 4.
It goes like this :
It works on many columns but after row 36 it says ERROR 6 OVERFLOW

Thanks for guiding me

Sub CrosstableToFlat()
Application.ScreenUpdating = False
Dim DateOS As String
Dim LastCol1 As Integer
Dim LastRowA As Integer
Dim i As Integer
Dim j As Integer
Dim sht As Worksheet
Dim NewRowA As Integer

Set sht = ActiveSheet

DateOS = Range("A2").Value
LastRowA = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
LastCol1 = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column



For i = 4 To LastCol1
    NewRowA = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1
    Range("D2:D" & LastRowA).Copy 'Copy Column D
    Range("C" & NewRowA).PasteSpecial Paste:=xlPasteValues 'Paste Col D in C
    Range("A2:B" & LastRowA).Copy
    Range("A" & NewRowA).PasteSpecial Paste:=xlPasteValues
    Columns(4).EntireColumn.Delete
Next i
      '  Range("A2:A" & LastRowA).Copy
       ' Range("B" & LastRowA + 1).PasteSpecial Paste:=xlPasteValues
       Application.ScreenUpdating = True
End Sub

 

[joeu2004]

To answer your question ("... but where?"), make the following temporary changes:

Sub CrosstableToFlat()
[....]
Dim NewRowA As Integer

On Error GoTo Oops

Set sht = ActiveSheet
[....]
       Application.ScreenUpdating = True

Exit Sub
Oops:
Stop
Resume
End Sub

When the error occurs, VBA will stop on the Stop statement. Press f8 twice to execute Stop and Resume and to return to the statement at fault.

If you still do not know what is wrong, let us know what the statement is, and tell us the values of the variables in the faulty line.

One suggestion that might actually fix the problem: Change As Integer to As Long. There is usually no benefit to using type Integer (or Single) these days. And using them can cause problems.

 

[jbesclapez]

God... simply changing to Long as solved the issue.

I do not understand.
Please can explain? why ??
I mean it is always an integer... so...

 

[RoryA]

There are a lot more rows on a worksheet than an Integer can hold (it only goes up to 32767). You should never use an Integer as a row number variable for that reason.

 

[jbesclapez]

There are a lot more rows on a worksheet than an Integer can hold (it only goes up to 32767). You should never use an Integer as a row number variable for that reason.

Ohhh..... did not know that limitation. Thanks a lot RoryA.
Can you explain me why this 32767 amount? and not another one? Is it linked with 32bits?... or 2^n

 

[RoryA]

or 2^n

Yes. It spans -32766 to 32767 - i.e. 2^16

 

[joeu2004]

The value range is -32768 to 32767: -(2^15) to 2^15 - 1. Because type Integer is a signed (so-called twos-complement) 16-bit value.

From the VBA help page: ``Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767``.

In contrast, type Long is a 32-bit value with a range from -(2^31) to 2^31 - 1: -2,147,483,648 to 2,147,483,647 .

 

[jbesclapez]

Thanks you All! It is super clear!