Share via

Run-time Error '6': Overflow During Macro Run

Anonymous
2012-11-19T16:06:35+00:00

Hi All,

I just completed a macro for work that essentially does a looped check between two lists, the lists contain 5 columns worth of data and the macro compares and matches the lists together. What I'm running into is an overflow error when the data set is too large, it's a bit tricky to figure out what "too large" is but I'm hoping to find a work around.

Details:

Records

List A: 56,708

List B: 1,500

When I run the check it always hits the error after the 10th check from List B (it matches list B to List A). Now if I shrink list A down to something smaller (not sure exactly what, but I know that if I do just the letter A it has about 2,931 records and then I can run the script on a much much larger List B (over 30,000 records).

Ultimately I'd like to run a check on my entire data set which would be 56,708 records for A and then about 900,000 records for B. I don't understand why it would fail on an overflow error when it's just a simple if check (it compares 5 points from List B to List A and then if there isn't a match at any point it goes to the next record, just a simple loop).

Any suggestions greatly appreciated

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-11-19T16:14:50+00:00

    Hi,

    This sounds like you have dimensioned a variable as INTEGER. An integer variable will fail with an OVERFLOW error if it's value exceeds 32767.

    DIM your variables as LONG and see if the problem goes away. If it doesn't then post your code.

    200+ people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2012-11-19T16:17:48+00:00

    Without seeing any of your macro code, I am assuming that you have a variable that is doing the looping ?

    Is this variable defined as an Integer ?

    If it is, then its maximum value would be 32,767

    Define the looping variable as a Long and it should work OK

    100+ people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-08-09T04:40:50+00:00

    Hi 

    I am also facing the same issue while i am filtering the content from one sheet and passing it on to second sheet in the specified column.

    there are about 20+ If conditions to check the data and the code works fine for 40 rows and then it throws  "overflow error"

    I can not post the code publicly.

    Thanks in Advance

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-11-19T16:19:43+00:00

    Going to try it as soon as this run is done, this sounds like it's probable....and not the first time I've made this mistake ;)

    I'll report back as soon as I do the check. Thanks to both of you

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2012-11-19T16:27:35+00:00

    Not the first time I've made this mistake. Seems to have solved it....now time to make it more efficient and do some cleanup work.

    Thanks to both of you

    0 comments