VBA Error 6 Overflow : But where?

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
214
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
To answer your question ("... but where?"), make the following temporary changes:

Rich (BB code):
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

Board Regular
Joined
Feb 6, 2010
Messages
214
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Board Regular
Joined
Feb 6, 2010
Messages
214
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
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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 .
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,100,042
Messages
5,472,142
Members
406,806
Latest member
babarfirasat

This Week's Hot Topics

Top