+ Reply to Thread
Results 1 to 7 of 7

Overflow Error 6 on Divide by 2

  1. #1
    Registered User
    Join Date
    01-24-2021
    Location
    Winchester, England
    MS-Off Ver
    Office 365 Excel for Mac v16.63
    Posts
    6

    Overflow Error 6 on Divide by 2

    I've been developing a macro that needs to put values into a one-dimensional array for filtering a table by month. In some places, my code works and in others, the same piece of code returns an Overflow Error. The problem occurs when I divide the loop counter by two. This division returns the Overflow error.
    I have distilled the problem code into a separate macro in a new workbook and I am still getting the error. The macro in its entirety is:

    Please Login or Register  to view this content.
    The error occurs at the line
    Debug.Print "j divided by 2 is " & j / 2

    For some reason, dividing the even integer j by 2 returns an overflow error.
    In this macro, the maximum value of j is 6, so this should not cause an integer variable to overflow. Only even numbers are divided by 2, so the result should always be an integer.

    Things I have already tried:
    1. Declaring j as types other than Integer: Long, Double,
    2. Changing the Debug.Print line to print Int(j / 2)
    3. Changing the Debug.Print line to print Int(j) / 2
    4. Leaving j as Integer and converting the value in j to a new variable, i, which is Long or Double


    Anyone any idea why this error is occurring and how to solve it?
    Attached Files Attached Files
    Last edited by johnv64; 01-24-2021 at 07:42 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,743

    Re: Overflow Error 6 on Divide by 2

    Code tags were added by OP - Thanks
    Last edited by Pepe Le Mokko; 01-24-2021 at 11:10 AM.

  3. #3
    Registered User
    Join Date
    01-24-2021
    Location
    Winchester, England
    MS-Off Ver
    Office 365 Excel for Mac v16.63
    Posts
    6

    Re: Overflow Error 6 on Divide by 2

    Apologies for the oversight. I have added the Code tags.
    Thanks.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,587

    Re: Overflow Error 6 on Divide by 2

    I do not replicate the error. When I run your code as is, it runs through the loop 3 times and outputs the values as expected. I get no "overflow" or other errors. Are you sure the problem is in this particular procedure, or could it be somewhere else in your Excel environment? Something different between the sample procedure and your actual procedure?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,425

    Re: Overflow Error 6 on Divide by 2

    Works fine for me in 16.46. Which version of macOS are you running?
    Rory

  6. #6
    Registered User
    Join Date
    01-24-2021
    Location
    Winchester, England
    MS-Off Ver
    Office 365 Excel for Mac v16.63
    Posts
    6

    Re: Overflow Error 6 on Divide by 2

    Thanks for your replies, MrShorty and rorya.

    My Mac is running Catalina 10.15.7

    I know that the code itself is not the problem, it is something else. In my full procedure I had an IF statement with two options and the code was in the first option and ran without a problem (still does in that version). I then moved the code outside the IF statement and started getting the Overflow error.

    I had a similar problem with a “For i = 1 To n” loop which I resolved by changing the i to a j (both Dim’d as Integer)

    When I could not find anything that worked, I restarted my Mac to try to reset everything and created a new worksheet with a new macro, typed in by hand so that there would be nothing odd copied if I cut and pasted. This is what I have shared in the post. When I run the macro, I still get the Overflow error.

    Any ideas on what might be causing this would be appreciated.

    Thanks.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,425

    Re: Overflow Error 6 on Divide by 2

    I'm somewhat stumped to be honest. Your workbook works fine for me, so can't be an issue with the workbook. That would suggest a problem with your machine, but you say the same code still works in the original version of your procedure, which tends to suggest it can't be an issue with the machine per se. I have seen a few mentions of peculiar issues with VBA on Macs with different data types - eg using a Single doesn't work, but a Double does, even though the values in use are well within range for either one. If you declare the variable as a Double does it make any difference? Or declare j as Long and use 2& for the constant to make it long too?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error 6 - Overflow. What now?
    By jolink in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-04-2020, 10:45 AM
  2. [SOLVED] Ribbon Customization error (Long Data Type but still overflow error)
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-30-2018, 08:15 AM
  3. Overflow error, can't see why
    By Alexander_V in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 11:57 AM
  4. Explanation of the Run-time error '6': Overflow Error
    By mgphill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2012, 10:46 AM
  5. Want to do a while-loop but get error message: run error '6' overflow
    By danzi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2011, 01:48 PM
  6. Overflow error, need help
    By mkerstei in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2006, 10:20 PM
  7. [SOLVED] Overflow error
    By Jim Berglund in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1