-
Dec 11th, 2007, 09:08 AM
#1
Thread Starter
New Member
[RESOLVED] Runtime error 6 "Overflow" -how do I fix this?
Hi there...
I'm trying to make a code that should decide if a person is "Man" or "Woman"
The range consists of numbers. If a number is equal = "Woman" and unequal = "Man".
When I run the following code i get the runtime error 6:
Code:
Sub Task1()
Dim CPRrange As Range
Dim Checkcell As Range
Dim i As Integer
Dim NumberOfRows As Integer
Workbooks.Open ThisWorkbook.Path & "\medlemskartotek.xls"
'CPR-digits are chosen:
Set CPRrange = Range("B2", Range("B2").End(xlDown))
NumberOfRows = CPRrange.Rows.Count
For Each Checkcell In CPRrange
'Tjeck for women:
If Checkcell.Value Mod 2 = 0 Then
Checkcell.Offset(0, 5).Value = "Woman"
'Tjeck for men:
Else
Checkcell.Offset(0, 5).Value = "Man"
End If
Next Checkcell
End Sub
Can anyone please help me how to correct this?
Best Regards
Andreas
Last edited by si_the_geek; Dec 11th, 2007 at 09:10 AM.
Reason: added code tags
-
Dec 11th, 2007, 09:13 AM
#2
Re: Runtime error 6 "Overflow" -how do I fix this?
Welcome to VBForums
The "Overflow" error means that you are trying to put a number into a variable (or property etc), and the data type of the variable doesn't allow numbers that large.
As you didn't mention where the error occurred, we need to basically guess.. I would assume it is on the line "NumberOfRows = CPRrange.Rows.Count", in which case you can correct it by changing the declaration of NumberOfRows from "As Integer" (up to 32K) to "As Long" (up to billions).
-
Dec 11th, 2007, 09:26 AM
#3
Thread Starter
New Member
Re: Runtime error 6 "Overflow" -how do I fix this?
Thanks... this forum seems to be just what i've been looking for!
My code fails in the following line:
If Checkcell.Value Mod 2 = 0 Then
Must be something with my modulus function... but i'm not expirienced enough to figure it out.
All the values for this Checkcell are 10-digit numbers. (ex. 2102862123)
All values that are unequal (as ex. above) are "men".
-
Dec 11th, 2007, 09:36 AM
#4
Re: Runtime error 6 "Overflow" -how do I fix this?
As it loops through the range... either Debug.PRint or MsgBox your Checkcell.Value ... I'm willing to bet that it is not what you think it is.
-tg
-
Dec 11th, 2007, 09:39 AM
#5
Re: Runtime error 6 "Overflow" -how do I fix this?
From what I can tell in the help, the Mod function only works with numbers up to the limit of the Long data type - so the highest value allowed is 2,147,483,647
There have been replacements for Mod posted on the forums to deal with that, but I can't seem to find any at the moment.
In this particular case however there is an easy work-around, which is to divide the number by two, and see if the result is a whole number, eg:
Code:
If (Checkcell.Value / 2) = CLng(Checkcell.Value / 2) Then
This will only double the limit, so may not work for all of your numbers.. an alternative is to use bit manipulation to see if the number is odd, which you can do like this:
Code:
If (Checkcell.Value And 1) = False Then
(the part in brackets checks the value of the final bit, which effectively tests if it is an Odd number)
-
Dec 11th, 2007, 09:47 AM
#6
Thread Starter
New Member
Re: Runtime error 6 "Overflow" -how do I fix this?
I've now done so...
The first value Checkcell encounters is 0912754451 and here it works fine and goes through the:
Else
Checkcell.Offset(0, 5).Value = "Man"
End If
By the next value og Checkcell: 2707851341 it displays the runtime error...
I've tried to Dim NumberOfRows As Long and the problem is still there...
-
Dec 11th, 2007, 09:54 AM
#7
Re: Runtime error 6 "Overflow" -how do I fix this?
Have you tried the alternatives I suggested in my last post?
-
Dec 11th, 2007, 10:05 AM
#8
Thread Starter
New Member
Re: Runtime error 6 "Overflow" -how do I fix this?
Thanks Si!
I had not seen your post with alternatives as i wrote the last post.
It now works!
Thanks!
I really appreciate your help, mate!
/Andreas
-
Dec 11th, 2007, 10:08 AM
#9
Re: Runtime error 6 "Overflow" -how do I fix this?
I'm happy to help.
As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)
You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".
-
Dec 11th, 2007, 10:52 AM
#10
Re: [RESOLVED] Runtime error 6 "Overflow" -how do I fix this?
Another alternative, since all you care about is if the number is odd/even.... is to take the last digit, and mod that..... would have resulted in the same result.
-tg
-
Oct 10th, 2014, 05:32 PM
#11
New Member
Re: Runtime error 6 "Overflow" -how do I fix this?
Originally Posted by si_the_geek
Welcome to VBForums
The "Overflow" error means that you are trying to put a number into a variable (or property etc), and the data type of the variable doesn't allow numbers that large.
As you didn't mention where the error occurred, we need to basically guess.. I would assume it is on the line "NumberOfRows = CPRrange.Rows.Count", in which case you can correct it by changing the declaration of NumberOfRows from "As Integer" (up to 32K) to "As Long" (up to billions).
THANK YOU SO MUCH!!!!
I have just registered in this forum to say this!!
This was new to me... never thought about that type sizes issue.. but since i've started to work with >100K rows... makes totally scense!!
Maybe this will help me in the future. Rephrasing, YOU did help me for the upcoming
Thanks once again si_the_geek you are the MAN!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|