Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
Hello folks,
I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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 "Phuelgod" skrev i meddelelsen ... Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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" wrote in message ... Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
Nevermind. I incorrectly declared variable types...should have used Long,
not Integer. -- Frank "Do or do not; There is no try" -Yoda "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
Hi,
the limitation is in the data type, an integer can be a max of + - 32767 dim the variable as long. Mike "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
You need to declare k and J as long not integer. There is a limit of 64536
rows in excel 2003. "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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 "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
Try declaring Long rather than Integer
-- Gary''s Student - gsnu200822 "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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. "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
Have you tried declaring as LONG. Take a look at the HELP files for INTEGER
and LONG -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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. -- A. Ch. Eirinberg "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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. -- A. Ch. Eirinberg "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
"Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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 "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
If you Dim j and/or k As Integer you will probably get overflow.
I would Dim them As Long. "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 6 - Overflow
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 "Phuelgod" wrote: Hello folks, I've got a runtime error 6 on the following line of code: For k = 2 To j local values a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error 6 overflow when running a macro | Excel Programming | |||
runtime error '6' overflow | Setting up and Configuration of Excel | |||
runtime error '6' overflow | Excel Programming | |||
Runtime Error 6 Overflow | Excel Programming | |||
Help! Overflow Error 6 | Excel Programming |