Runtime Error 6 - Overflow

[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

 

[Per Jessen]

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

 

[Bernie Deitrick]

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

 

[Phuelgod]

Nevermind. I incorrectly declared variable types...should have used Long,
not Integer.

 

[Mike H]

Hi,

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

Mike

 

[Joel]

You need to declare k and J as long not integer. There is a limit of 64536
rows in excel 2003.

 

[FSt1]

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

 

[Gary''s Student]

Try declaring Long rather than Integer

 

[RonaldoOneNil]

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.

 

[Barb Reinhardt]

Have you tried declaring as LONG. Take a look at the HELP files for INTEGER
and LONG

 

[Howard31]

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.

 

[Howard31]

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.

 

[JMB]

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

 

[PetLahev]

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

 

[Premek]

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

 

[JLGWhiz]

If you Dim j and/or k As Integer you will probably get overflow.

I would Dim them As Long.

 

[Billy Liddel]

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

 

[Shasur]

Hi

Integers can hold upto 32767 (i.e, -32,768 to 32,767)

Try using Long

Cheers
Shasur