Runtime Error 6 - Overflow

  • Thread starter Thread starter Phuelgod
  • Start date Start date
P

Phuelgod

Hello folks,

I've got a runtime error 6 on the following line of code:

For k = 2 To j

local values are:
k = 23468
j = 32933

both are declared integers

In a nutshell, the code find out how many rows of data there are, then goes
through each row to count the data by type (Select Case Left(Cells(k,2),2)).

Is there a limit to iterations on a for...next loop?

Any help troubleshooting this will be appreciated

Thanks!

Frank
 
Hello Frank

The problem is your variable type.

Integer variables can only hold values from -32,768 to 32,767. Define the
varibles as Long, as it can hold values up to 2,147,483,647

Regards,
Per
 
Frank,

Use

Dim j As Long

Integers are limited to 2^15, or 32,768.

It is generally a good practice to use Long for any variable stepping
through rows....

HTH,
Bernie
MS Excel MVP
 
Hi,

the limitation is in the data type, an integer can be a max of + - 32767
dim the variable as long.

Mike
 
You need to declare k and J as long not integer. There is a limit of 64536
rows in excel 2003.
 
hi
in vb help, type "data type".
interger - 2 bytes = -32768 to 32767
your values = 23468 and 32933
32933 exceeds the value of and interger. that is your overflow problem.
change from interger to long if no decimal, double or single if decimal. see
data type in vb help.

regards
FSt1
 
k is your loop counter and changes through each iteration so how can it equal
23468 ?

Anyway, your problem is that k & j as integers can only hold a maximum
number of 32768

You need to declare them as long.
 
Hi Phuelgod,

Strictly speaking there is no limit to iterations exept for the memory your
computer has. The error you get is because as you said 'j' has been declared
as a Integer, a Integer type can only be asigned values ranging from -32,768
to 32,767 as your 'j' variable needs to be asigned values bigger than that
i.e. 32933 you get an error - Solution - Simple: declare the 'j' variable to
Long type instead of integer. The Long type can hold values ranging from
-2,147,483,648 to 2,147,483,647.
 
Declare 'j' as Long not Integer as integer can handle values in the range of
-32,768 to 32,767 - Where as Long can hold values in the range of
-2,147,483,648 to 2,147,483,647.
 
From VBA help:

"Integer variables are stored as 16-bit (2-byte) numbers ranging in value
from
-32,768 to 32,767. "

try declaring j as long, which can range value from -2,147,483,648 to
2,147,483,647
 
Phuelgod said:
Hello folks,

I've got a runtime error 6 on the following line of code:

For k = 2 To j

local values are:
k = 23468
j = 32933

both are declared integers

In a nutshell, the code find out how many rows of data there are, then goes
through each row to count the data by type (Select Case Left(Cells(k,2),2)).

Is there a limit to iterations on a for...next loop?

Any help troubleshooting this will be appreciated

Thanks!

Frank
Click to expand...
 
Hey man

data of type Integer has limit - upper limit for Integer is 32,767
So, you have to change your variable types to Long
Then it will work

Premek
 
I see there are many replies but the details have not been uploaded.
Christmas Eh?

Here is another solution, perhaps this will be uploaded.

Sub test()
Dim c As Variant, d As Variant
Dim left2 As String
Dim x() As Variant
Dim vaX() As Variant
Dim bIsUnique As Boolean
Dim iCodeCount As Variant
Dim sCode As String
Dim iIndex As Integer
Dim Inti As Integer
Dim nUnique As Integer
Dim nx As Integer
Dim wks As Worksheet
Dim rngDest As Range
Dim rngSource As Range

nx = 0
Set rngSource = Range(Selection.Address)
For Each c In rngSource
bIsUnique = False
If Not IsEmpty(c) Then
sCode = Left(c, 2)
End If
For iIndex = 1 To nUnique
If sCode = x(iIndex) Then
bIsUnique = True
GoTo AddCode 'Exit For Next loop
End If
Next iIndex
'Create unique array of codes
AddCode:
If Not bIsUnique And Not IsEmpty(sCode) Then
nUnique = nUnique + 1
ReDim Preserve x(nUnique)
x(nUnique) = sCode
End If

Next c
' enter the types in column 4
For iIndex = 1 To UBound(x)
Cells(iIndex, 4) = x(iIndex)
Next iIndex

Set rngDest = Range(Cells(1, 4), Cells(nUnique, 4))
' enter the cont of types in column 5
For Each d In rngDest
iCodeCount = 0
For Each c In rngSource
sCode = Left(c, 2)
If CStr(d) = CStr(sCode) Then
iCodeCount = iCodeCount + 1
End If
Next c

d.Offset(0, 1) = iCodeCount

Next d

End Sub

Regards
Peter Atherton
 
Back
Top