Results 1 to 11 of 11

Thread: [RESOLVED] Runtime error 6 "Overflow" -how do I fix this?

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2007
    Posts
    7

    Resolved [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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    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).

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2007
    Posts
    7

    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".

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    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)

  6. #6

    Thread Starter
    New Member
    Join Date
    Dec 2007
    Posts
    7

    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...

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Runtime error 6 "Overflow" -how do I fix this?

    Have you tried the alternatives I suggested in my last post?

  8. #8

    Thread Starter
    New Member
    Join Date
    Dec 2007
    Posts
    7

    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

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    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".

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    New Member
    Join Date
    Oct 2014
    Posts
    1

    Re: Runtime error 6 "Overflow" -how do I fix this?

    Quote Originally Posted by si_the_geek View Post
    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
  •  



Click Here to Expand Forum to Full Width