Excel 2010 Run Time error '6' Overflow

On the FG program, when a sheet is sent to post, it adjusts inventory for each item, but when it records the items into the "Past History" sheet, I get the error message. It puts a "P" beside the "POST" button on the right side of the page. Any protected sheets use "CHUCK" as the password. The posting has been working until today. Can you spot the problem?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can't find a procedure called FG. Which procedure and which line does it fail on?
NorieAnalyst Assistant Commented:
Is the problem sub Post?
CRHIVAuthor Commented:
In the FG.xls, there are sheets 1-20 that orders are taken on. After the order is shipped, the "POST" button is pressed to update the inventory, then the items are added to the "PAST HISTORY" sheet as a record. The program then puts a "P" beside the "POST" button, so the user and the program knows it has been done. Then the user presses the "POST" button again. If the program sees the "P" beside the button, it asks the user if they want to clear the sheet for reuse. The error has just shown up today. I read where the "xlastrow" variable needed to be set to "Long", not "Integer". I tried that, but doesn't seem to work. I am resending the FG.xls.
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

NorieAnalyst Assistant Commented:
Which line of code is highlughted when the error happens?

If you can't tell us that can you tell us how to recreate the error?

Actually I found the line of code that's causing the problem
xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row

Open in new window

I also found this
    Dim xlastrow As Integer

Open in new window

When I change that to this the code doesn't error anymore.
    Dim xlastrow As Long

Open in new window

I also noticed something else.

You have this.

   z = Sheets("INVENTORY").Range("A65536").End(xlUp).row

Open in new window

But you've declared z as String.
Dim z As String

Open in new window

I think you should also declare that as Long.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CRHIVAuthor Commented:
xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row
Is the line that it has the hang up on. When I change "Dim xlastrow as long", I don't get an error, but it doesn't put the items into PAST HISTORY.
NorieAnalyst Assistant Commented:
That's a different issue really.

If you make the change to Long you can see what's happening in the code.

You can do that by settting a break-point on this line with F9,
xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row

Open in new window

then step through with F8.
CRHIVAuthor Commented:
How come when I change  " Range("A65536") " to " Range("A65000") ", on the line below, it works? Even without changing "Dim xlastrow as Integer" to "Dim xlastrow as Long"

xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row
NorieAnalyst Assistant Commented:
It's because you have data in rows 65468 to 65474.

So if you start at row 65000 and go up the next row with data is 8356, which can be stored as an Integer.

If you start at 65535 and go up the next row with data is 65474, which can be stored in and Integer.

I'm pretty sure that data shouldn't be there and it could actually explain why it appears items aren't getting put in the past history.

Any new data will be put below those rows.

I don't know how or when those rows got there but they should be removed, or moved up to join the other data.

You should still switch to using Long for xlastrow.

PS Instead of using 65536 in the code you can use Rows.Count. That will return the no of rows in a sheet and could help avoid typos.
CRHIVAuthor Commented:
Exactly how would I phrase the xlastrow statement to find the place to start adding data?
The line below returns xlastrow as 65534, and starts from there.

xlastrow = Sheets("Past History") . UsedRange.Rows.Count
CRHIVAuthor Commented:
And I removed the data from the bottom of the Past History sheet.
NorieAnalyst Assistant Commented:
You can use the code you originally have, just declare xlastrow as Long.

You don't need to use Rows.Count, but it can be helpful.

If you were to use it the code would look like this.
xlastrow = Sheets("Past History").Range("A" & Rows.Count).End(xlUp).Row

Open in new window

The important things are to delete/move that misplaced data and declare the variable as Long.
CRHIVAuthor Commented:
Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.